Importing from pdf to excel workbook using VBA and PowerQuery

[luki102]

Hi. I have problem with my code. I try to import tables from pdf file using VBA. But unfortunalety I have error about existing name. How to Change this code a get variable names i every new Worksheet?

Sub Makro2()
'
' Makro2 Makro
'
    
    
    ActiveWorkbook.Queries.Add Name:="Page001", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Źródło = Pdf.Tables(File.Contents(""C:\Users\osp20201145\Desktop\Faktury PGE makro\Faktury PGE\EDI123456 YYY Energia Ciepła 9031044197_JRM 123.PDF""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Page1 = Źródło{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Nagłówki o podwyższonym poziomie"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Zmieniono typ"" = Table." & _
        "TransformColumnTypes(#""Nagłówki o podwyższonym poziomie"",{{""[image]"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Warszawa, 07.10.2022"", type date}, {""Colum" & _
        "n12"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Zmieniono typ"""
    
    ActiveWorkbook.Worksheets.Add
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Page001;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Page001]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Page001"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[John_w]

Welcome to MrExcel forums.

Try this - I've modified your macro to create a unique query name and use that name in the ActiveWorkbook.Queries.Add, the Source string for the ActiveSheet.ListObjects.Add and the CommandText and DisplayName strings. With no queries in the workbook, when you run the macro for the first time the first query is named "Page001" (the same as your code). On subsequent runs the query is named "Page001_2", "Page001_3", etc.

Sub Makro2()
'
' Makro2 Makro
'
    Dim queryName As String
    
    queryName = "Page001"
    If ActiveWorkbook.Queries.Count > 0 Then
        queryName = queryName & "_" & ActiveWorkbook.Queries.Count + 1
    End If
    
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Zródlo = Pdf.Tables(File.Contents(""C:\Users\osp20201145\Desktop\Faktury PGE makro\Faktury PGE\EDI123456 YYY Energia Ciepla 9031044197_JRM 123.PDF""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Page1 = Zródlo{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Naglówki o podwyzszonym poziomie"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Zmieniono typ"" = Table." & _
        "TransformColumnTypes(#""Naglówki o podwyzszonym poziomie"",{{""[image]"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Warszawa, 07.10.2022"", type date}, {""Colum" & _
        "n12"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Zmieniono typ"""
    
    ActiveWorkbook.Worksheets.Add
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & queryName & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & queryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = queryName
        .Refresh BackgroundQuery:=False
    End With
    
End Sub