vba microsoft.mashup.oledb.1 provider is not registered on the local machine

[cmlobo_sr_sf]

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

[#2]

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

[#3]

@cmlobo_sr_sf 

 I face the same issue too not sure how to resolve it . using the Power Query

[#4]

@cmlobo_sr_sf 

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! 

[#5]

Same issue.

[#6]

Same issue here! I have a bunch of automated reports that have all stopped working!

[#7]

@cmlobo_sr_sf 

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".

[#8 · Accepted solution]

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

[#9]

@GaryCheng 

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.

[#10]

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.