VBA to enable Power Query

[ajw5173]

Hi,

I have a workbook that opens on the Microsoft scheduler. The workbook requires Power Query to run correctly and for some reason it wont load power query before it starts processing. Is there any way to make power query load with <acronym title="visual basic for applications">VBA</acronym> or any other possibly workaround to get this to work?

Thanks!

 

[Matt Allington]

What do you mean "load power query". Is the addin installed and enabled? If so, there is nothing to "load". Are you trying to refresh the workbook? You can use power update Power Update Software - Excelerator BI

 

[ajw5173]

When I use the scheduler to open up a workbook that runs a macro on open Power Query doesn't load.

 

[Matt Allington]

what do you mean "load"? Are you saying that there is no Power Query menu? That is what load means. Is there a Power Query menu when you open the workbook yourself?
Or are you instead saying that it doesn't refresh? If so, are you triggering the refresh event in Power Query?

 

[ajw5173]

There is no power query menu on top while the query is running. The query fails because it says there is no PQ. I then hit debug and PQ pops up and if I try to run the macro again everything works.

 

[Matt Allington]

And is the Power Query menu there when you open it normally? If so, it sounds like Power Query is not installed for all users, and maybe the System Tasks is using a different user account. Just a guess

 

[hildafdez]

And is the Power Query menu there when you open it normally? If so, it sounds like Power Query is not installed for all users, and maybe the System Tasks is using a different user account. Just a guess

This is also happening to me, the problem as Microsoft says in this post is that the Add-in doesn't always load. I have it configured to always load, but that doesn't always happen (opening Excel normally or through a VBA process) the solution is disable it and enable it again so the ribbon appears. Since I'm doing an automated update of my workbook through a VBA macro, I would need to do this procedure of disable and enable the Power Query Add-In in VBA, any idea how?

 

[hildafdez]

Hello!

After doing some more research I found the post http://www.mrexcel.com/forum/excel-questions/451239-disabling-com-add.html I added to my code the VBA instructions to Disable and Enable the Power Query COM Add-In that is posted on the MS Support page to solve the ribbon not appearing even though the Add-In is supposed to be enabled. I'll be testing it tomorrow when the Windows Scheduler runs, but looks like it does the trick.

[FONT=lucida console]Application.COMAddIns("Microsoft.Mashup.Client.Excel").Connect = False 'Disable Power Query[/FONT]
[FONT=lucida console]
[/FONT]
[FONT=lucida console]Application.COMAddIns("Microsoft.Mashup.Client.Excel").Connect = True  'Enable Power Query[/FONT]

Hope it helps ajw5173!!!