Forum Discussion
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. After closing the form, the data updates correctly (currentlyd, the connection is set to refresh on open, but the macro also includes refresh-codes)
The issue seems to be, that .NET framework doesn't load until after the workbook_open() event (and associated macros) have completed. Is there any way to mitigte this?
3 Replies
Refresh an external data connection in Excel
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 ConnVBA 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.
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.
Please try it with this info, maybe this will help you.
Unfortunately, I can't help anymore, because my Latin or knowledge is at the end.Connection properties
Nikolino
I know I don't know anything (Socrates)
