May 13 2019 04:06 AM
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)
```
May 13 2019 07:13 AM
May 13 2019 07:24 AM
@Jan Karel Pieterse No credentials are required, all source directories/workbooks are located on the same machine.
@Jan Karel Pieterse wrote:
Which credentials have you set the task scheduler to use to run this? You may have to provide yours for them to work?
May 13 2019 07:39 AM
May 13 2019 07:56 AM - edited May 13 2019 08:44 AM
@Jan Karel Pieterse Yes, the task scheduler run with my user credentials; the specific directory is D:\Tableau_Share\TableauSources\ and a lot of subdirectories from here. I should note, however, that all files involved were copied via robocopy from another machine and from another user, from whom we are migrating this whole structure. Perhaps this can raise the issues inside Power Query?
If so, what should be done?
Thanks in advance,
May 20 2019 05:01 AM
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).
May 20 2019 07:56 AM