Forum Discussion

MartinRavn's avatar
MartinRavn
Copper Contributor
Sep 16, 2020

Refresh data connections in workbook_open() event

Hi   I have created a small macro which needs to refresh a SQL Power Query before displaying an input form.    When i open the workbook though, the form displays but without the updated data. Aft...
excel
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Sep 16, 2020

MartinRavn 

Refresh an external data connection in Excel

https://support.microsoft.com/en-gb/office/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440?ui=en-us&rs=en-gb&ad=gb

 

or with this VBA code

 

For Each Conn In wbTagesbericht.Connections
  ' This causes Excel to wait for the query to be updated
  Conn.OLEDBConnection.BackgroundQuery = False
  Application.StatusBar = "The query "& Conn.Name &" is updated. Please wait..."
  Conn.OLEDBConnection.Refresh
  Conn.OLEDBConnection.BackgroundQuery = True
Next Conn

 

VBA code was not tested for functionality.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

  • MartinRavn's avatar
    MartinRavn
    Copper Contributor
    Sep 16, 2020

    NikolinoDE 

    Thank you for your input. 

     

    I have tried setting the connection to refresh on startup, however this refresh happens after the Workbook_open()-macro has finished, and thus after the data required in the form needs to be loaded. 

     

    I have tried the "for each..." loop as well, but since .net framework (or some other function) hasn't loaded at the time of running the workbook_open(), the connections are not updated. 

Resources