Power Query DataTable does not load

by

I used Excel VBA to run a PowerQuery procedure which transforms and loads the data into a table on my Excel worksheet.

The scenario is that i have a PDF file (multiple pages) which contains some tables with data. I recorded a macro and used it to pass variables.

So, first in my code, i call a procedure [DisplayListTable] to load the PDF file which gives me the name of the pages individually along with its type (whether page or table). Then i use it to call another procedure to give me each page table’s data and process the information.

I am a bit new to it and am having the following problem involving Excel VBA with PowerQuery:

Everything works fine when i step over[F8] the code or use breakpoint after executing the PowerQuery procedure.
However, when i run the code in one go[F5], nothing gets loaded into the data. It displays as “ExternalData_1: Getting Data…”

I tried to add wait, refresh, DoEvents and disable/enable events/screenupdating but nothing happens.

Could anyone help me please?

The following code is the procedure DisplayListTable:

    Sub DisplayListTable(v_TableName as string v_Filename as string)

    'delete preexisting queries
    on error resume next
    thisworkbook.queries(v_TableName).Delete
    on error goto 0
    
    DoEvents

    thisworkbook.activate
    thisworkbook.queries.add name:=v_TableName; formula:= _
    "let" & chr(13) & "" & chr(10) & "    Source = Pdf.Tables(File.Contents(""" & v_FileName & """), [Implementation=""1.3""])" & chr(13) & "" & chr(10) & "in" & chr(13) & "" & chr(10) & "    Source"
    thisworkbook.worksheets("LIST_OF_TABLE").select
    With activesheet.ListObjects.add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & v_TableName & """;Extended Properties="""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & v_TableName & "]")
        .RowNumbers = false
        .FillAdjacentFormulas = false
        .PreserveFormatting = true
        .RefreshOnFileOpen = false
        '.BackgroundQuery = true
        .BackgroundQuery = false
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = true
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = true
        .Listobject.Name = v_TableName
        .Refresh
    End With
    
    End Sub

  • 2

    Suggest adding your code (or the relevant portion of it) so that others can try to reproduce the problem or identify mistakes that your code may contain? See this SO page.

    – 

  • Thanks JohnM, i have edited and provide part of my code.

    – 

  • 1

    I have been able to find a solution) I have included a DoEvents before running the query and set the BackgroundQuery to false. That worked! Thanks again (for just viewing and closing without a reasonable explanation)

    – 




  • I didn’t close your question … I don’t have the rep to do so and, given you added your code and further details, I don’t agree with it being closed … but there is nothing I can do about it

    – 

Leave a Comment