TransformColumnTypes without knowing column name

[trose6382]

Hello,
I'm trying to find a way around knowing the column name of a pdf file I'm trying to upload to Excel. Right now, the code works as long as I know what the first column name is. Unfortunately, the pdf files I upload data from changes the first column name. Is there a way for me to upload data from a pdf and transform the column types without knowing the name of the column being imported? The column name in question is in red and called UnknownColumnName. Thank you in advance!

Sub Upload_PDF_Data()
   
    ' Upload_PDF_Data Macro
   
    Dim PDFtableName As String, tableId As String
    Dim PDFfile As Variant
    Dim loadToCell As Range
    Dim queryName As String
   
    'The name of the table in the selected PDF which will be imported into Excel
   
    PDFtableName = "Page001"
   
    'The cell where the import will begin - the destination cell of the query table created by this macro
   
    Set loadToCell = ActiveSheet.Range("I113")
       
    PDFfile = Application.GetOpenFilename(FileFilter:="PDF Files (*.pdf), *.pdf", MultiSelect:=False, Title:="Select PDF to upload")
    If PDFfile = False Then Exit Sub 'Cancel clicked
   
    queryName = GetQueryName(CStr(PDFfile))
    tableId = PDFtableName
   
 
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Pdf.Tables(File.Contents(""" & PDFfile & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
        "    Page1 = Source{[Id=""" & tableId & """]}[Data]," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{??UnknownColumnName??, type text}, {""Column2"", type number}, {""Column3"", Int64.Type}, {""Column4"", Int64.Type}, {""Column5"", type number}})" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"""
       
       
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & queryName & """;Extended Properties=""""", _
            Destination:=loadToCell).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

 

[RoryA]

You could use

Table.ColumnNames(#""Promoted Headers""){0}

to get the first column name

 

[trose6382]

It works! Thank you so much!

 

[epayret]

¡Funciona! ¡Muchas gracias!

Hola trosa6382

Estoy tratando de resolver lo mismo que tu preguntaste.

¿Podrías colocar el código de como quedo de acuerdo a la respuesta de RoryA?

Saludos