Forum Discussion
vba microsoft.mashup.oledb.1 provider is not registered on the local machine
Hello,
some moths ago the following vba script stop working:
sub auto_open
ActiveWorkbook.Connections("Query - Query1").Refresh
end sub
the error message is :
microsoft.mashup.oledb.1 provider is not registered on the local machine
Query1 its a power query connection
The Problem is that when the excel starts que .NET Framework isn't loaded.
the command : ActiveWorkbook.RefreshAll , works but i have multiple connections and want them updated in a specific order
i tried calling the command bar :
Application.CommandBars("Queries and Connections").Visible=True
it does not work
I tried loading the command bar using sendkeys :
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
Sub refresh_sequence()
ActiveWorkbook.Sheets(1).Select
SendKeys "%apnl", True
Sleep 1000
ActiveWorkbook.Connections("Query - Query1").Refresh
ActiveWorkbook.Connections("Query - Query3").Refresh
ActiveWorkbook.Connections("Query - Query2").Refresh
End Sub
the sendkeys when alone it loads the command bar and NET Framework, but when i combined sendkeys with query Refresh, never got the net framework loaded before query refresh command could be executed.
I spent hour trying to find a solution but nothing ... i suspect that my inicial code spot working because an update of office 365
my office version is:
Version 2009 (build 13130.20000 click-to-Run)
Beta Channel
This excel files are automated reportes this a specific refresh sequence and now i cant update automatically, i am updating them manually and i am losing my mind
Best Regards
Carlos
Hi Carlos,
May I recommend you to have the following changes:
1. Update auto_open as follow
sub auto_open
ActiveWorkbook.RefreshAll
end sub
2. Uncheck "Refresh this connection on Refresh All" property for all the queries you want to refresh by code (i.e. Query1, Query2 etc.)
This solution works for me.
Cheers,
Brian
Hello cmlobo_sr_sf ,
Have you found a solution to this problem? I am experiencing the same issue.
Any help would greatly be appreciated.
Thanks
I believe something in the latest update made it so the .netframework doesn't automatically load on workbooks that contain queries.
I got around this by adding a blank query and setting it to load on open. This way the framework loads when you open the book.
I run many things from a vbscript in the background and this 'bug' broke them all!
- Thanks. This was the workaround for me too. I created a query that calls on a single-cell named range in my workbook, and have it set to connection only. Then, I set the properties to refresh on file open. It works.
I think the issue is that when it's not loaded at open, then my last query (which is the one my macro calls on) tries to run the query without refreshing its parent query properly. Somehow, this blank query starts the framework as you described, and then allows the child query to refresh the parent query properly. That's my best guess.
Hi Carlos,
May I recommend you to have the following changes:
1. Update auto_open as follow
sub auto_open
ActiveWorkbook.RefreshAll
end sub
2. Uncheck "Refresh this connection on Refresh All" property for all the queries you want to refresh by code (i.e. Query1, Query2 etc.)
This solution works for me.
Cheers,
Brian
I face the same issue too not sure how to resolve it . using the Power Query
Same issue.
Same issue here! I have a bunch of automated reports that have all stopped working!
I found a way that seems to enable my macro to work again, though im not sure why it works.
The method is:-
Go to the Queries connection and for the queries that had been setup, right-click and choose "properties" - under the refresh control , choose to tick the "Enable Fast Data Load" and untick the "Refresh this connection on Refresh All".
I had the same issue as well. I tried something similar to your solution above, and it worked. I have >150 queries so would be unfeasible to apply your solution to each. See my solution below
Go to Data -> Get Data -> Query Options
Tick "Fast Data Load"
when I did this and saved my workbook all the macros started working again!
hope this helps others as well.
