[xpost][/xpost]
Hi Need help with this code. It is very messy, because I recorded the macro, but I need help to get the filepath, that i have in a cell called "filePath", in the File.Contents function. Thanks for your help.
Code
Sub TestMacro()
Dim CellAdress As String
CellAdress = Range("filePath").Value
ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""CellAdress""), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & " Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Appended Query"" = Table.Combine({#""Promoted Headers"", #""Table002 (" & _
"Page 2)""})," & Chr(13) & "" & Chr(10) & " #""Removed Duplicates"" = Table.Distinct(#""Appended Query"")," & Chr(13) & "" & Chr(10) & " #""Removed Other Columns"" = Table.SelectColumns(#""Removed Duplicates"",{""Varenummer"", ""Antal""})," & Chr(13) & "" & Chr(10) & " #""Added Custom"" = Table.AddColumn(#""Removed Other Columns"", ""Custom"", each null)," & Chr(13) & "" & Chr(10) & " #""Reordered Columns"" = Table.ReorderColumns(#""Added Custom"",{""Custom"", ""Va" & _
"renummer"", ""Antal""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Reordered Columns"""
ActiveWorkbook.Queries.Add Name:="Table002 (Page 2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""CellAdress""), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & " Table002 = Source{[Id=""Table002""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Table002, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Promoted Headers"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table001__Page_1"
.Refresh BackgroundQuery:=False
End With
End Sub