Hello. I'm trying to create an Excel application to order inventory for a business. I need to input certain columns from a PDF table that's in three tables. So I need to extract three columns from one PDF that's broken into three tables. I need help! Any and all help would be greatly appreciated. Here's what I have so far.
This code is what i got when recording a macro and some code i had started.
This code is what i got when recording a macro and some code i had started.
VBA:
Sub GetDataFromFile()
Dim PDFTabkeName As String, TableId As String
Dim PDFfile As Variant
Dim loadToCell As Range
Dim queryName As String
Dim i As Integer
'The name of the table in the selected PDF which will be imported into Excel
'The cell where the import will begin - the destination cell of the query table created by this macro
Set loadToCell = ActiveSheet.Range("A1")
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 = Left(PDFtableName, InStr(PDFtableName, " "))
'The cell where the import will begin - the destination cell of the query table created by this macro
ActiveWorkbook.Queries.Add Name:="Table002 (Page 2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""" & PDFfile & """), [Implementation=""1.3""])," & vbCrLf & _
" #""Changed Type"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type number}, {""Colu" & _
"mn4"", type number}, {""Column5"", type number}, {""Column6"", type number}, {""Column7"", type number}, {""Column8"", type number}, {""Column9"", type number}, {""Column10"", type number}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Column3"", ""Column4"", ""Column5"", ""Column6"", ""Column7"", ""Column8"", ""Column10""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Remo" & _
"ved Columns"""
ActiveWorkbook.Queries.Add Name:="Table003 (Page 3)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""" & PDFfile & """), [Implementation=""1.3""])," & vbCrLf & _
"Table003 = Source{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table003,{{""Column1"", Int64.Type}, {""Column2"", type text}, {""Column3"", type number}, {""Col" & _
"umn4"", type number}, {""Column5"", type number}, {""Column6"", type number}, {""Column7"", type number}, {""Column8"", type number}, {""Column9"", type number}, {""Column10"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Queries.Add Name:="Append1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Table.Combine({#""Table001 (Page 1)"", #""Table002 (Page 2)"", #""Table003 (Page 3)""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
Workbooks("Truck Ordering Help.xlsm").Connections.Add2 _
"Query - Table001 (Page 1)", _
"Connection to the 'Table001 (Page 1)' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table001 (Page 1);Extended Properties=" _
, """Table001 (Page 1)""", 6, True, False
Workbooks("Truck Ordering Help.xlsm").Connections.Add2 _
"Query - Table002 (Page 2)", _
"Connection to the 'Table002 (Page 2)' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table002 (Page 2);Extended Properties=" _
, """Table002 (Page 2)""", 6, True, False
Workbooks("Truck Ordering Help.xlsm").Connections.Add2 _
"Query - Table003 (Page 3)", _
"Connection to the 'Table003 (Page 3)' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table003 (Page 3);Extended Properties=" _
, """Table003 (Page 3)""", 6, True, False
Workbooks("Truck Ordering Help.xlsm").Connections.Add2 "Query - Append1(1)", _
"Connection to the 'Append1' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Append1;Extended Properties=" _
, """Append1""", 6, True, False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Query - Table001 (Page 1)"), Version:=8). _
CreatePivotChart(ChartDestination:="Table001 (Page 1)").Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartStyle = 201
ActiveChart.Parent.Delete
Application.CutCopyMode = False
ActiveWorkbook.Connections("Query - Append1(1)").Refresh
With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
Connections("Query - Append1(1)"), Destination:=Range("$A$1")).TableObject
.RowNumbers = False
.PreserveFormatting = True
.RefreshStyle = 1
.AdjustColumnWidth = True
.ListObject.DisplayName = "Append1_2"
.Refresh
End With
Range("Append1_2[[#Headers],[Column1]]").Select
Application.CommandBars("Queries and Connections").Visible = False
Range("Append1_2[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Itenm"
Range("Append1_2[[#Headers],[Itenm]]").Select
ActiveCell.FormulaR1C1 = "ItemNo"
Range("Append1_2[[#Headers],[Column2]]").Select
ActiveCell.FormulaR1C1 = "Itemn"
Range("Append1_2[[#Headers],[Itemn]]").Select
ActiveCell.FormulaR1C1 = "Item"
Range("Append1_2[[#Headers],[Column9]]").Select
ActiveCell.FormulaR1C1 = "Target Yield"
Columns("D:I").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=0
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Rows("2:3").Select
Selection.Delete Shift:=xlUp
Range("46:46,51:51").Select
Range("A51").Activate
ActiveWindow.SmallScroll Down:=15
Range("46:46,51:51,55:55,66:66").Select
Range("A66").Activate
ActiveWindow.SmallScroll Down:=32
Rows("88:88").Select
ActiveWindow.SmallScroll Down:=44
Range("88:88,135:135").Select
Range("A135").Activate
ActiveWindow.SmallScroll Down:=-97
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-2
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-4
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-4
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=0
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=0
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-7
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.SmallScroll Down:=0
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-1
ActiveWindow.SmallScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=0
ActiveWindow.SmallScroll ToRight:=0
ActiveWindow.SmallScroll Down:=-4
Rows("88:88").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=26
Rows("134:134").Select
Selection.Delete Shift:=xlUp
Rows("18:18").EntireRow.AutoFit
Rows("2:18").Select
Range("A18").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C136").Select
Selection.End(xlUp).Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("E154").Select
Set loadToCell = TICRLoad.Range("A2")
PDFfile = Application.GetOpenFilename(FileFilter:="PDF Files (*.pdf), *.pdf", MultiSelect:=False, Title:="Select PDF to upload")
If PDFfile = False Then Exit Sub 'Cancel clicked
End Sub