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

%3CLINGO-SUB%20id%3D%22lingo-sub-1579120%22%20slang%3D%22en-US%22%3Evba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1579120%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esome%20moths%20ago%20the%20following%20vba%20script%20stop%20working%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esub%20auto_open%3C%2FP%3E%3CP%3EActiveWorkbook.Connections(%22Query%20-%20Query1%22).Refresh%3C%2FP%3E%3CP%3Eend%20sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20error%20message%20is%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Emicrosoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuery1%20its%20a%20power%20query%20connection%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20Problem%20is%20that%20when%20the%20excel%20starts%20que%20.NET%20Framework%20isn't%20loaded.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20command%20%3A%26nbsp%3BActiveWorkbook.RefreshAll%20%2C%20works%20but%20i%20have%20multiple%20connections%20and%20want%20them%20updated%20in%20a%20specific%20order%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20tried%20calling%20the%20command%20bar%20%3A%3C%2FP%3E%3CP%3EApplication.CommandBars(%22Queries%20and%20Connections%22).Visible%3DTrue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20does%20not%20work%3C%2FP%3E%3CP%3EI%20tried%20loading%20the%20command%20bar%20using%20sendkeys%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Declare%20PtrSafe%20Sub%20Sleep%20Lib%20%22kernel32%22%20(ByVal%20dwMilliseconds%20As%20LongPtr)%20'For%2064%20Bit%20Systems%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESub%20refresh_sequence()%3C%2FP%3E%3CP%3EActiveWorkbook.Sheets(1).Select%3C%2FP%3E%3CP%3ESendKeys%20%22%25apnl%22%2C%20True%3CBR%20%2F%3ESleep%201000%3CBR%20%2F%3E%3CBR%20%2F%3EActiveWorkbook.Connections(%22Query%20-%20Query1%22).Refresh%3CBR%20%2F%3EActiveWorkbook.Connections(%22Query%20-%20Query3%22).Refresh%3CBR%20%2F%3EActiveWorkbook.Connections(%22Query%20-%20Query2%22).Refresh%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20sendkeys%20when%20%3CU%3Ealone%3C%2FU%3E%20it%20loads%20the%20command%20bar%20and%20NET%20Framework%2C%20but%20when%20i%20combined%20sendkeys%20with%20query%20Refresh%2C%20never%20got%20the%20net%20framework%20loaded%20before%20query%20refresh%20command%20%3CSPAN%3Ecould%20be%20executed.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20spent%20hour%20trying%20to%20find%20a%20solution%20but%20nothing%20...%20i%20suspect%20that%20my%20inicial%20code%20spot%20working%20because%20an%20update%20of%20office%20365%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Emy%20office%20version%20is%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EVersion%202009%20(build%2013130.20000%20click-to-Run)%3CBR%20%2F%3EBeta%20Channel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20excel%20files%20are%20automated%20reportes%20this%20a%20specific%20refresh%20sequence%20and%20now%20i%20cant%20update%20automatically%2C%20i%20am%20updating%20them%20manually%26nbsp%3Band%20i%20am%20losing%20my%20mind%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBest%20Regards%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECarlos%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1579120%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1602980%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602980%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755074%22%20target%3D%22_blank%22%3E%40cmlobo_sr_sf%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20found%20a%20solution%20to%20this%20problem%3F%20I%20am%20experiencing%20the%20same%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20greatly%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1633982%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1633982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755074%22%20target%3D%22_blank%22%3E%40cmlobo_sr_sf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20face%20the%20same%20issue%20too%20not%20sure%20how%20to%20resolve%20it%20.%20using%20the%20Power%20Query%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1648093%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755074%22%20target%3D%22_blank%22%3E%40cmlobo_sr_sf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20something%20in%20the%20latest%20update%20made%20it%20so%20the%20.netframework%20doesn't%20automatically%20load%20on%20workbooks%20that%20contain%20queries.%3C%2FP%3E%3CP%3EI%20got%20around%20this%20by%20adding%20a%20blank%20query%20and%20setting%20it%20to%20load%20on%20open.%20This%20way%20the%20framework%20loads%20when%20you%20open%20the%20book.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20run%20many%20things%20from%20a%20vbscript%20in%20the%20background%20and%20this%20'bug'%20broke%20them%20all!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1648567%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648567%22%20slang%3D%22en-US%22%3E%3CP%3ESame%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1650046%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650046%22%20slang%3D%22en-US%22%3E%3CP%3ESame%20issue%20here!%20I%20have%20a%20bunch%20of%20automated%20reports%20that%20have%20all%20stopped%20working!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1651345%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1651345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755074%22%20target%3D%22_blank%22%3E%40cmlobo_sr_sf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20a%20way%20that%20seems%20to%20enable%20my%20macro%20to%20work%20again%2C%20though%20im%20not%20sure%20why%20it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20method%20is%3A-%3C%2FP%3E%3CP%3EGo%20to%20the%20Queries%20connection%20and%20for%20the%20queries%20that%20had%20been%20setup%2C%20right-click%20and%20choose%26nbsp%3B%20%22properties%22%20-%20under%20the%20refresh%20control%20%2C%20choose%20to%20tick%20the%20%22Enable%20Fast%20Data%20Load%22%20and%20untick%20the%20%22Refresh%20this%20connection%20on%20Refresh%20All%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22GaryCheng_0-1599709098938.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217442i4288287882EAD260%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22GaryCheng_0-1599709098938.png%22%20alt%3D%22GaryCheng_0-1599709098938.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1663187%22%20slang%3D%22en-US%22%3ERe%3A%20vba%20microsoft.mashup.oledb.1%20provider%20is%20not%20registered%20on%20the%20local%20machine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1663187%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Carlos%2C%3C%2FP%3E%3CP%3EMay%20I%20recommend%20you%20to%20have%20the%20following%20changes%3A%3C%2FP%3E%3CP%3E1.%20Update%20auto_open%20as%20follow%3C%2FP%3E%3CP%3Esub%20auto_open%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3BActiveWorkbook.RefreshAll%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eend%20sub%3C%2FP%3E%3CP%3E2.%20Uncheck%20%22Refresh%20this%20connection%20on%20Refresh%20All%22%20property%20for%20all%20the%20queries%20you%20want%20to%20refresh%20by%20code%20(i.e.%20Query1%2C%20Query2%20etc.)%3C%2FP%3E%3CP%3EThis%20solution%20works%20for%20me.%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBrian%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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

 

 

7 Replies
Highlighted

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

Highlighted

@cmlobo_sr_sf 

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

Highlighted

@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! 

Highlighted
Highlighted

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

 

 

Highlighted

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

 

GaryCheng_0-1599709098938.png

 

 

Highlighted

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