Sep 16 2020 12:38 AM
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?
Sep 16 2020 02:26 AM
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.
Sep 16 2020 02:42 AM
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.
Sep 16 2020 04:22 AM
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.
Nikolino
I know I don't know anything (Socrates)