'Pulling data from pdf to Excel using VBA
I have a more than 1000 pdf files and I want to extract/pull data from pdf. If I use the pdf to excel conversion then it will take long time so I am trying pull data from pdf to excel using vba.
The format in PDF are as follows : Year 20XX Month MAR
Unique Identification Number(UIN) Name of the Company
Particulars Value IncomeTax IndirectTax OtheTaxes Sales 20000 1000 10000 500 Purchases 20000 500 500 0 Exempt 3000 0 0 0
The format in which I require in Excel is as follows :
Year Month UIN NameofthCompany Particularss Value IncomeTax Indirect OtherTaxes
Solution 1:[1]
There are tools online that do this for you. Also, if you want to consider converting all PDF files in a folder to text files, you can easily import text all text files into a folder into Excel, and then manipulate any way you want. A PDF file is essentially a text file, on steroids. Post back if you have additional questions.
How to import multiple text files from a folder into one worksheet?
Sub Test()
'UpdatebyExtendoffice6/7/2016
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | ASH |