Refresh data connections in workbook_open() event

%3CLINGO-SUB%20id%3D%22lingo-sub-1672025%22%20slang%3D%22en-US%22%3ERefresh%20data%20connections%20in%20workbook_open()%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1672025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20small%20macro%20which%20needs%20to%20refresh%20a%20SQL%20Power%20Query%20before%20displaying%20an%20input%20form.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20i%20open%20the%20workbook%20though%2C%20the%20form%20displays%20but%20without%20the%20updated%20data.%20After%20closing%20the%20form%2C%20the%20data%20updates%20correctly%20(currentlyd%2C%20the%20connection%20is%20set%20to%20refresh%20on%20open%2C%20but%20the%20macro%20also%20includes%20refresh-codes)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20seems%20to%20be%2C%20that%20.NET%20framework%20doesn't%20load%20until%20after%20the%20workbook_open()%20event%20(and%20associated%20macros)%20have%20completed.%20Is%20there%20any%20way%20to%20mitigte%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1672025%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1672544%22%20slang%3D%22de-DE%22%3ESubject%3A%20Refresh%20data%20connections%20in%20workbook_open()%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1672544%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795381%22%20target%3D%22_blank%22%3E%40MartinRavn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CH1%20id%3D%22toc-hId-530155028%22%20id%3D%22toc-hId-530155028%22%3E%3CFONT%20size%3D%222%22%3ERefresh%20an%20external%20data%20connection%20in%20Excel%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Frefresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Frefresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eor%20with%20this%20VBA%20code%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFor%20Each%20Conn%20In%20wbTagesbericht.Connections%0A%20%20'%20This%20causes%20Excel%20to%20wait%20for%20the%20query%20to%20be%20updated%0A%20%20Conn.OLEDBConnection.BackgroundQuery%20%3D%20False%0A%20%20Application.StatusBar%20%3D%20%22The%20query%20%22%26amp%3B%20Conn.Name%20%26amp%3B%22%20is%20updated.%20Please%20wait...%22%0A%20%20Conn.OLEDBConnection.Refresh%0A%20%20Conn.OLEDBConnection.BackgroundQuery%20%3D%20True%0ANext%20Conn%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EVBA%20code%20was%20not%20tested%20for%20functionality.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

@MartinRavn 

Refresh an external data connection in Excel

https://support.microsoft.com/en-gb/office/refresh-an-external-data-connection-in-excel-1524175f-777...

 

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.

Highlighted

@Nikolino 

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. 

Highlighted

@MartinRavn 

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

https://support.microsoft.com/en-us/office/connection-properties-9d3599a9-e9b3-461d-99b2-c5505ddae6e...

 

Nikolino
I know I don't know anything (Socrates)