Forum Discussion
PowerQuery fail when run via VBA
Hello everyone!
My scenario is like this:
I must schedule an xlsm file to run on a remote machine, every night, which basically opens 30+ excel files with power queries to be updated on specific worksheets; this is achieved by the following expression:
```vba
If SelectRow <> "" And SelectColumn<> "" Then
Cells(SelectRow, SelectColumn).Select
End If
On Error Resume Next
Selection.PivotTable.PivotCache.Refresh
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
DoEvents
Application.CalculateUntilAsyncQueriesDone
Application.Wait DateAdd("n", Esperar, Now)
xlBook.Save
xlBook.Close True
```
The mentioned power queries connect to excel files and folders within the same machine, with local paths set up.
Note that the queries themselves WORK, if run manually. I could even run this macro once with a remote session open, but they invariably fail when run via scheduler or when I close the remote session.
The code will fail with the following errors. Could anyone please advise?
Thanks a lot,
```
************** Exception Text **************
Microsoft.Mashup.Client.UI.Shared.Com.ComWrapperException: Cannot cast null to type 'System.Double'. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Mashup.Client.UI.Shared.Com.ComWrapper.As[T](Object value)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Client.UI.Shared.Com.ComWrapper.As[T](Object value)
at Microsoft.Mashup.Client.Excel.Com.ExcelComWrapper.AsIntFromDouble(Object value)
at Microsoft.Mashup.Client.Excel.Com.ApplicationFeatures..ctor(IApplication application)
at Microsoft.Mashup.Client.Excel.Com.Application..ctor(Object application, Boolean enableEvents)
at Microsoft.Mashup.Client.Excel.AddIn.TryCreateApplication(Object appObject, IApplication& application)
at Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnConnection(Object application, ext_ConnectMode connectMode, Object addInInst, Array& custom)
```
```
************** Exception Text **************
Microsoft.Practices.Unity.ResolutionFailedException: Resolution of the dependency failed, type = "Microsoft.Mashup.Host.Document.IApplicationConstants", name = "(none)".
Exception occurred while: while resolving.
Exception is: InvalidOperationException - The current type, Microsoft.Mashup.Host.Document.IApplicationConstants, is an interface and cannot be constructed. Are you missing a type mapping?
-----------------------------------------------
At the time of the exception, the container was:
Resolving Microsoft.Mashup.Host.Document.IApplicationConstants,(none)
---> System.InvalidOperationException: The current type, Microsoft.Mashup.Host.Document.IApplicationConstants, is an interface and cannot be constructed. Are you missing a type mapping?
at Microsoft.Practices.ObjectBuilder2.DynamicMethodConstructorStrategy.ThrowForAttemptingToConstructInterface(IBuilderContext context)
at BuildUp_Microsoft.Mashup.Host.Document.IApplicationConstants(IBuilderContext )
at Microsoft.Practices.ObjectBuilder2.BuildPlanStrategy.PreBuildUp(IBuilderContext context)
at Microsoft.Practices.ObjectBuilder2.StrategyChain.ExecuteBuildUp(IBuilderContext context)
at Microsoft.Practices.Unity.UnityContainer.DoBuildUp(Type t, Object existing, String name, IEnumerable`1 resolverOverrides)
--- End of inner exception stack trace ---
at Microsoft.Practices.Unity.UnityContainer.DoBuildUp(Type t, Object existing, String name, IEnumerable`1 resolverOverrides)
at Microsoft.Practices.Unity.UnityContainer.Resolve(Type t, String name, ResolverOverride[] resolverOverrides)
at Microsoft.Practices.Unity.UnityContainerExtensions.Resolve[T](IUnityContainer container, ResolverOverride[] overrides)
at Microsoft.Mashup.Host.Document.DependencyInjectionService.Resolve[T]()
at Microsoft.Mashup.Host.Document.ClientConfig.ClientConfigManager..ctor()
at Microsoft.Mashup.Host.Document.ClientConfig.ClientConfigManager.get_Instance()
at Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnDisconnection(ext_DisconnectMode disconnectMode, Array& custom)
```
6 Replies
I ended up solving this and will share the solution in case anyone faces a similar issue in the future:
The problem was running the macro on auto open, because Excel starts running your code before loading the add ins, so it was running a power query refresh without the power query addin being loaded.
The solution was to open excel, wait for 10 seconds, and only then open the workbook. If automation is required, some code must be written to open Excel, wait 10 seconds, then open the workbook by sending shortcut keystrokes (ALT, O, O, etc).
- Aha! I think you can also solve this by not calling the refresh macro from Auto_Open directly, but by scheduling it from Auto_Open using Application.OnTime. This is an often used solution because indeed Excel triggers Workbook_Open or Auto_Open even before it has finished all of its startup tasks. Using Application.OnTime solves this as -even if you schedule it 0 seconds from now- this is not processed before Excel has finished with all of that.
- Which credentials have you set the task scheduler to use to run this? You may have to provide yours for them to work?
JKPieterse No credentials are required, all source directories/workbooks are located on the same machine.
JKPieterse wrote:
Which credentials have you set the task scheduler to use to run this? You may have to provide yours for them to work?- In which folders are the files? If they are anywhere in your user path, the task will have to run using YOUR credentials.
