refresh data before running macro

  • Im running a macro in excel externally (from access) using data from an access query.

    The macro copies a table of data and pastes it into word.

    When excel opens it will run the code before it has had time to refresh the data. Code shown here:

    Note: in the connection properties I have enabled "refresh data when opening file".

  • Waiting 20 seconds here does nothing. If i manually open the document before calling the macro in access the table does update.

    Dim Ticker As Range
     Application.Wait (Now + TimeValue("00:00:20"))
        Set Ticker = Range(Cells(4, 1), Cells(65, 8))
        Sheets("Paste Special").Select
        Cells(4, 1).PasteSpecial xlPasteValues
  • Hey Home,

    I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more options.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!