none
Excel 2013 - refreshing Power Query data in batch mode using VBA RRS feed

  • Question

  • Hello,

    I'm trying to refresh data from power query in batch mode (Schedule night) using VBA. First I had problem because I'm using the 64 bits version. But that is OK now. My big problem is that excel doesn't wait the end of the refresh before closing.

    Then I have tried to use different method to wait. One of the best one is DoEvent, an Other is a loop on all queries use OLE DB Connection objects with background refresh set to false.

    In all cases I have errors.

    This is my macro with the doevent solution :

    #If Win64 Then
     Private Declare PtrSafe Function GetCommandLineL Lib "kernel32" _
         Alias "GetCommandLineA" () As LongPtr
     Private Declare PtrSafe Function lstrcpyL Lib "kernel32" _
         Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As LongPtr) As Long
     Private Declare PtrSafe Function lstrlenL Lib "kernel32" _
         Alias "lstrlenA" (ByVal lpString As LongPtr) As Long
     #Else
     Private Declare Function GetCommandLineL Lib "kernel32" _
         Alias "GetCommandLineA" () As Long
     Private Declare Function lstrcpyL Lib "kernel32" _
         Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long
     Private Declare Function lstrlenL Lib "kernel32" _
         Alias "lstrlenA" (ByVal lpString As Long) As Long
     #End If
     
    Function GetCommandLine() As String
       Dim strReturn As String
       #If Win64 Then
       Dim lngPtr As LongPtr
       #Else
       Dim lngPtr As Long
       #End If
       Dim StringLength As Long
       'Get the pointer to the commandline string
       lngPtr = GetCommandLineL
       'get the length of the string (not including the terminating null character):
       StringLength = lstrlenL(lngPtr)
       'initialize our string so it has enough characters including the null character:
       strReturn = String$(StringLength + 1, 0)
       'copy the string we have a pointer to into our new string:
       lstrcpyL strReturn, lngPtr
       'now strip off the null character at the end:
       GetCommandLine = Left$(strReturn, StringLength)
     End Function
     
    
    Private Sub Workbook_Open()
    Dim macmdline As Variant
    Dim monparam As Variant 'déclare une variable
    Dim lTest As Long, cn As WorkbookConnection
     
        
        macmdline = GetCommandLine() 'affecte la valeur de la ligne de commande
        If Not IsNull(macmdline) Then 'si la variable est nulle
    
              
            If Len(macmdline) > 0 Then 'on s'assure qu'il y a eu une ligne de commande passée
    
                If InStr(macmdline, "/cmd") > 0 Then
                    macmdline = Replace(macmdline, ThisWorkbook.FullName, "", , , vbTextCompare)
                    monparam = Split(macmdline, "/cmd")
                    
                    ActiveWorkbook.RefreshAll
                    'Attend la fin du refresh
                    DoEvents
                    
                    Application.DisplayAlerts = False
                    With ThisWorkbook
                            .Save
                    End With
                    
                    DoEvents
                    Application.Quit
                    
                End If
            End If
        End If
    End Sub
    
    

    Détails of the .net error message is :

    Consultez la fin de ce message pour plus de détails sur l'appel du débogage
    juste-à-temps (JIT) à la place de cette boîte de dialogue.
    
    ************** Texte de l'exception **************
    Microsoft.Mashup.Client.ClientShared.Com.ComWrapperException: Cannot cast null to type 'System.Double'. ---> System.NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.
       à Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
       --- Fin de la trace de la pile d'exception interne ---
       à Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
       à Microsoft.Mashup.Client.Excel.Com.ExcelComWrapper.AsIntFromDouble(Object value)
       à Microsoft.Mashup.Client.Excel.Com.ApplicationFeatures..ctor(IApplication application)
       à Microsoft.Mashup.Client.Excel.Com.Application..ctor(Object application, Boolean enableEvents)
       à Microsoft.Mashup.Client.Excel.AddIn.TryCreateApplication(Object appObject, IApplication& application)
       à Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnConnection(Object application, ext_ConnectMode connectMode, Object addInInst, Array& custom)
    
    
    ************** Assemblys chargés **************
    mscorlib
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.100.0 built by: NETFXREL2STAGE
        CodeBase : file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll
    ----------------------------------------
    Microsoft.Office.Excel.DataModel
        Version de l'assembly : 15.0.0.0
        Version Win32 : 15.0.4777.1000
        CodeBase : file:///C:/Program%20Files/Common%20Files/Microsoft%20Shared/OFFICE15/DataModel/Microsoft.Office.Excel.DataModel.dll
    ----------------------------------------
    System
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
    ----------------------------------------
    System.Data
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
    ----------------------------------------
    System.Core
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
    ----------------------------------------
    Microsoft.Excel.Amo
        Version de l'assembly : 11.0.0.0
        Version Win32 : 11.0.2830.77 ((BI_O15_OfficeBox_CU).150723-0305 )
        CodeBase : file:///C:/Program%20Files/Common%20Files/Microsoft%20Shared/OFFICE15/DataModel/Microsoft.Excel.Amo.DLL
    ----------------------------------------
    System.Configuration
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
    ----------------------------------------
    System.Xml
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.1067.0 built by: NETFXREL3STAGE
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
    ----------------------------------------
    System.Numerics
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
    ----------------------------------------
    System.Drawing
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.1068.2 built by: NETFXREL3STAGE
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    ----------------------------------------
    Microsoft.Excel.AdomdClient
        Version de l'assembly : 11.0.0.0
        Version Win32 : 11.0.2830.77 ((BI_O15_OfficeBox_CU).150723-0305 )
        CodeBase : file:///C:/Program%20Files/Common%20Files/Microsoft%20Shared/OFFICE15/DataModel/Microsoft.Excel.AdomdClient.DLL
    ----------------------------------------
    System.Transactions
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
    ----------------------------------------
    System.Data.resources
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Data.resources/v4.0_4.0.0.0_fr_b77a5c561934e089/System.Data.resources.dll
    ----------------------------------------
    mscorlib.resources
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/mscorlib.resources/v4.0_4.0.0.0_fr_b77a5c561934e089/mscorlib.resources.dll
    ----------------------------------------
    Microsoft.Mashup.Client.Excel
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Excel.dll
    ----------------------------------------
    Extensibility
        Version de l'assembly : 7.0.3300.0
        Version Win32 : 7.00.9466
        CodeBase : file:///C:/Windows/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
    ----------------------------------------
    office
        Version de l'assembly : 15.0.0.0
        Version Win32 : 15.0.4805.1003
        CodeBase : file:///C:/Windows/assembly/GAC_MSIL/office/15.0.0.0__71e9bce111e9429c/office.dll
    ----------------------------------------
    Microsoft.Mashup.Client.Windows
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Windows.DLL
    ----------------------------------------
    Microsoft.Mashup.Document
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Document.DLL
    ----------------------------------------
    Microsoft.MashupEngine
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.MashupEngine.DLL
    ----------------------------------------
    System.Windows.Forms
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    ----------------------------------------
    Microsoft.Mashup.ProviderShared
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.ProviderShared.DLL
    ----------------------------------------
    Microsoft.Office.Interop.Excel
        Version de l'assembly : 15.0.0.0
        Version Win32 : 15.0.4805.1003
        CodeBase : file:///C:/Windows/assembly/GAC_MSIL/Microsoft.Office.Interop.Excel/15.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Excel.dll
    ----------------------------------------
    Microsoft.Mashup.Client.Models
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Models.DLL
    ----------------------------------------
    Microsoft.Mashup.EventSource
        Version de l'assembly : 1.0.0.0
        Version Win32 : 2.29.4217.1861
        CodeBase : file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.EventSource.DLL
    ----------------------------------------
    System.Windows.Forms.resources
        Version de l'assembly : 4.0.0.0
        Version Win32 : 4.6.81.0 built by: NETFXREL2
        CodeBase : file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms.resources/v4.0_4.0.0.0_fr_b77a5c561934e089/System.Windows.Forms.resources.dll
    ----------------------------------------
    
    ************** Débogage JIT **************
    Pour activer le débogage juste-à-temps (JIT), le fichier de configuration pour cette
    application ou cet ordinateur (machine.config) doit avoir la valeur
    jitDebugging définie dans la section system.windows.forms.
    L'application doit également être compilée avec le débogage
    activé.
    
    Par exemple :
    
    <configuration>
        <system.windows.forms jitDebugging="true" />
    </configuration>
    
    Lorsque le débogage juste-à-temps est activé, les exceptions non gérées
    seront envoyées au débogueur JIT inscrit sur l'ordinateur
    plutôt que d'être gérées par cette boîte de dialogue.
    
    
    
    HELP !!!

    Tuesday, March 22, 2016 6:10 PM

Answers

  • Hey Serge, the issue you are running into is a timing issue. Power Query updates the status of its connections  through polling, and likely the workbook is getting closed before the status update polling cycle is run.

    In your macro it appears that DoEvents should let the status update, but it does not let the UI thread run and keeps the polling from running. 

    What works best is to set a callback timer using Application.OnTime so polling can work while the timer is waiting to callback.  Here is an example of code that works well.

    Sub RefreshPQConnectionsandClose()
        For Each cn In Application.ActiveWorkbook.Connections
         isPowerQueryConnection = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1") > 0
         If isPowerQueryConnection Then
            cn.OLEDBConnection.BackgroundQuery = False
            cn.Refresh
         End If
        Next cn
         Application.OnTime DateAdd("s", 10, Now), "CloseIt"
    End Sub
    
    Sub CloseIt()
         Application.ActiveWorkbook.Close (True)
    End Sub

    I am hoping you can modify this to make it work for your situation.

    Thanks,

    -jeff

    Note: orignally posted here https://social.technet.microsoft.com/Forums/en-US/497f8b78-b1a6-4aaa-a411-5938e78f6cef/refresh-power-query-via-vba?forum=powerquery

    Wednesday, November 8, 2017 7:43 PM

All replies

  • UPDATE: Original suggestion deleted, now that Jeff has answered below. - Ehren



    Thursday, March 24, 2016 8:22 PM
    Owner
  • I dont know how can this be considered an answer.

    I have the same error and I am using Power query, if I open the file manually no problem but if the file is opened through a batch, I get the same error posted above.

    excel cannot cast null to type system.double

    Wednesday, November 8, 2017 7:18 PM
  • Hey Serge, the issue you are running into is a timing issue. Power Query updates the status of its connections  through polling, and likely the workbook is getting closed before the status update polling cycle is run.

    In your macro it appears that DoEvents should let the status update, but it does not let the UI thread run and keeps the polling from running. 

    What works best is to set a callback timer using Application.OnTime so polling can work while the timer is waiting to callback.  Here is an example of code that works well.

    Sub RefreshPQConnectionsandClose()
        For Each cn In Application.ActiveWorkbook.Connections
         isPowerQueryConnection = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1") > 0
         If isPowerQueryConnection Then
            cn.OLEDBConnection.BackgroundQuery = False
            cn.Refresh
         End If
        Next cn
         Application.OnTime DateAdd("s", 10, Now), "CloseIt"
    End Sub
    
    Sub CloseIt()
         Application.ActiveWorkbook.Close (True)
    End Sub

    I am hoping you can modify this to make it work for your situation.

    Thanks,

    -jeff

    Note: orignally posted here https://social.technet.microsoft.com/Forums/en-US/497f8b78-b1a6-4aaa-a411-5938e78f6cef/refresh-power-query-via-vba?forum=powerquery

    Wednesday, November 8, 2017 7:43 PM
  • Thank you, it works fine when  I open the file manually, just like the other macro, but when I put it to a batch now I get this error:

    Runtime error'1004':

    Initialization of the data source failed

    Debug:

    cn.Refresh


    Wednesday, November 8, 2017 8:38 PM
  • Can explain a bit more how you are trying to run this?  I am not quite sure what you mean by "put it to a batch".  With more detail I may be able to provide a better solution. 

    Thanks,

    -jeff

    Wednesday, November 8, 2017 9:55 PM
  • The file is scheduled to run automatically, so either on a .BAT file or on the windows scheduler, either way I get the error when the file is loaded. but If i run the file manually the error does not happen.
    Wednesday, November 8, 2017 10:14 PM
  • I just tried it out, and I was able to run my workbook refresh from a workbook with the batch file code of 

    SET SCRIPT_PATH=%~dp0
    %SCRIPT_PATH%reproBatchRefresh.xlsm

    and workbook code of

    Private Sub Workbook_Open()
        RefreshPQConnectionsandClose
    End Sub
    
    Sub RefreshPQConnectionsandClose()
        For Each cn In Application.ActiveWorkbook.Connections
         isPowerQueryConnection = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1") > 0
         If isPowerQueryConnection Then
            cn.OLEDBConnection.BackgroundQuery = False
            cn.Refresh
         End If
        Next cn
         Application.OnTime DateAdd("s", 10, Now), "CloseIt"
    End Sub
    
    Sub CloseIt()
        Application.ActiveWorkbook.Save
        Application.Quit
    End Sub
    

    Could you be running the batch file in a user context (system user?) that doesn't have permission to the data source?  

    Wednesday, November 8, 2017 11:08 PM
  • I have permissions. I will try but what is on the batch:

    ~dp0

    Wednesday, November 8, 2017 11:25 PM
  • %~dp0 gets the drive and directory that the batch file is located in.
    Wednesday, November 8, 2017 11:37 PM
  • i definitely don't know how to run the file that way.
    Wednesday, November 8, 2017 11:44 PM
  • Ok just figured it out and works perfect with your  code thank you so much!
    Thursday, November 9, 2017 1:22 AM
  • That is super!!
    Saturday, November 11, 2017 12:54 AM