|
#1
|
|||
|
|||
Extract column from Pdf to Excel file
Hello Experts,
I want to extract specific column from PDF file to Excel File. i know there are many software and converter tools but my office PC i cannot use any online or converter software to use on my computer. Everyday there are many files in pdf as same format i want to type into excel sheet of specific columns its getting large time to type on excel. i attach three sample files what i need to copy in excel 1.original PDF file I receiving everyday 2.sample file extract to excel shows which are the column i need to get data into excel. 3. pdf to excel file show on highlight on yellow column are to copy from pdf file please help to solve this problems it will be great appreciated. if you need any further details i can explain it please help me... |
#2
|
|||
|
|||
Hi,
If you can not edit the PDF file then I do not think you will be able to do as you want. |
#3
|
|||
|
|||
Quote:
Many thanks after a long time i got the reply. yes its difficult convert from PDF to Excel but here i tried with some vba code to transfer from PDF to excel. First will convert to word and then it will copy to excel sheet the code was working fine in Word file but after import to excel file the column display is not showing correct. at least i dont want all the data to copy in excel i need only specific column to data to transfer in excel if possible have a look to solve this problems please.. Code:
Sub read_pdf_document_tables() Const PDFPath As String = "C:\Users\u\Downloads\maximoreport\ORIGINAL FILE.pdf" Dim sht As Worksheet Dim WDoc As Word.Document Dim WApp As Word.Application Dim i As Long, r As Long, c As Long Dim rng As Range, t As Word.Table Set WApp = CreateObject("Word.Application") WApp.Visible = True Set WDoc = WApp.Documents.Open(PDFPath, ConfirmConversions:=False, ReadOnly:=False) Set sht = Sheets("Temp") Set rng = sht.Range("A1") sht.Activate For Each t In WDoc.Tables t.Range.Copy rng.Select rng.Parent.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False With rng.Resize(t.Rows.Count, t.Columns.Count) .Orientation = xlLandscape .Cells.UnMerge Cells.Columns.AutoFit Cells.Rows.AutoFit End With Set rng = rng.Offset(t.Rows.Count + 2, 0) Next t WDoc.Close 'word file will close WApp.Quit ' word file will close End Sub |
#4
|
|||
|
|||
HI,
If you are able to use "Word" you should be able to gather the data that you need for Excel. However I do not have Adobe that allows me to Edit PDF documents. |
#5
|
|||
|
|||
Ok then thanks for the reply
|
#6
|
|||
|
|||
Hi,
You need to extract the data to a excel workbook. You can then be able to extract the data you require for it to the actual workbook. The following is a code that I used when I had a Trial copy of Adobe. Code:
Option Base 1 Sub CQCard_Process() Application.ScreenUpdating = False Dim myArray As Variant Dim Mycount As Long Dim CurFile As String Dim Destwb As Workbook Dim rng As Range Dim mydt As String Dim lrow As Long Dim Onr As String Dim sFileName As String myArray = Array("OrderNr.", "FileNr", "Indent", "Date Of Order") lrow = Range("A" & Rows.Count).End(xlUp).Row Const DirLoc As String = "C:\QCCard\" CurFile = Dir(DirLoc & "*.XLSX") ''' CHeck to see if you have QCCards to process.''' If CurFile = "" Then MsgBox "You do not have QC Cards to process!" Exit Sub End If Do While CurFile <> vbNullString ''' we open new workbook tehn populate it with the data CurFile = Dir(DirLoc & "*.XLSX") If CurFile = "" Then Exit Sub Dim Origwb As Workbook Set Destwb = Workbooks.Add Set Origwb = Workbooks.Open(Filename:=DirLoc & CurFile) ''Here we open each workbook in the file ''' Populate the data to new workbook ''' With Origwb .Sheets("Table 1").Range("AC8").WrapText = False .Sheets("Table 1").Range("AC3").WrapText = False .Sheets("Table 1").Range("v2").WrapText = False .Sheets("Table 1").Range("c1").WrapText = False .Sheets("Table 1").Range("AC8").MergeCells = False .Sheets("Table 1").Range("AC3").MergeCells = False .Sheets("Table 1").Range("v2").MergeCells = False .Sheets("Table 1").Range("c1").MergeCells = False End With Destwb.Activate Set rng = ActiveSheet.Range("A1:D1") With Destwb With Worksheets("Sheet1") For Mycount = 1 To UBound(myArray) .Cells(1, Mycount).Value = myArray(Mycount) Next Mycount End With Origwb.Sheets("Table 1").Range("AC8").Value = Replace(Right(Origwb.Sheets("Table 1").Range("AC8").Value, _ Len(Origwb.Sheets("Table 1").Range("AC8").Value) - InStr(Origwb.Sheets("Table 1").Range("AC8").Value, Chr(10))), " ", "") Origwb.Sheets("Table 1").Range("AC3").Value = Replace(Right(Origwb.Sheets("Table 1").Range("AC3").Value, _ Len(Origwb.Sheets("Table 1").Range("AC3").Value) - InStr(Origwb.Sheets("Table 1").Range("AC3").Value, Chr(10))), " ", "") Origwb.Sheets("Table 1").Range("c1").Value = Replace(Right(Origwb.Sheets("Table 1").Range("c1").Value, _ Len(Origwb.Sheets("Table 1").Range("c1").Value) - InStr(Origwb.Sheets("Table 1").Range("c1").Value, Chr(10))), " ", "") Origwb.Sheets("Table 1").Range("v2").Value = Replace(Right(Origwb.Sheets("Table 1").Range("v2").Value, _ Len(Origwb.Sheets("Table 1").Range("v2").Value) - InStr(Origwb.Sheets("Table 1").Range("v2").Value, Chr(10))), " ", "") mydt = Format(Origwb.Sheets("Table 1").Range("AC8").Value, "mm-dd-yy") .Sheets("Sheet1").Range("A2").Value = Trim(Mid(Origwb.Sheets("Table 1") _ .Range("AC3").Value, InStr(1, Origwb.Sheets("Table 1").Range("Ac3").Text, " #") + 1)) '''order .Sheets("Sheet1").Range("B2").Value = Trim(Mid(Origwb.Sheets("Table 1") _ .Range("V2").Value, InStr(1, Origwb.Sheets("Table 1").Range("V2").Text, " #") + 1)) .Sheets("Sheet1").Range("C2").Value = Trim(Mid(Origwb.Sheets("Table 1") _ .Range("C1").Value, InStr(1, Origwb.Sheets("Table 1").Range("C1").Text, ".") + 1)) '' Indent .Sheets("sheet1").Range("D2").Value = mydt ''' Date Title = Destwb.Sheets("Sheet1").Range("A2") & "_" & Destwb.Sheets("Sheet1") _ .Range("B2") & "_" & mydt sFileName = "C:\QCCard\QCCompleted\" & Title ActiveWorkbook.SaveAs Filename:=sFileName ', FileFormat:=51 ActiveWorkbook.Close SaveChanges:=True Application.EnableEvents = False '''''''' Move file to QCProcessed we will kill the original ActiveWorkbook.SaveAs "C:\QCCard\QCProcessed\" & Origwb.Name ActiveWorkbook.Close Kill "C:\QCCard\" & CurFile Application.EnableEvents = True End With ' Next i Loop Application.ScreenUpdating = True MsgBox "QCCard Run Complete" End Sub |
#7
|
|||
|
|||
Quote:
Hi charlesdh, as you given code i try but its getting error i am externally sorry if i didn't use your code properly because i just new on vba learning if so forgive me please try to solve my problems which i explained its very grateful to me and heads up to my boss please according to my requirement can u solve it or if you need more clarification we can discuss about it. Most of the people need to solve this kind of problems it will be helpful. Many thanks to take your time and knowledge my friend. can you please attached your sample workbook that i can understand easily. |
#8
|
|||
|
|||
Can you upload some sample file which you import to excel might be i can have some idea about to convert file
|
#9
|
|||
|
|||
HI,
If you have Adobe you can manually export it to "Excel". If you already have the exported file post it so we can help. There are example file for excel that should allow you to use excel code that will export the PDF file. Google something like "excel vba to export PDF to Excel" Last edited by charlesdh; 06-09-2017 at 01:07 PM. Reason: Added info |
#10
|
|||
|
|||
Quote:
Sorry for late reply. please refer the attached file as you mentioned i export the file from Nitro pdf converter. 1. orginal file.xlsx Excel file is the one i converted. 2. import column to file.xlsx the highlighted the yellow i need to pull the data from the original file. according to the matching column. Please if possible can you try it do something it will more great helpful to me. |
#11
|
|||
|
|||
One of the things I do in those situations is copy the relevant column(s) from PDF to Onenote as screenshot & copy the data from picture in Onenote & paste to excel
|
#12
|
|||
|
|||
Hi ferocious,
Thanks for your reply i think this way will be also fine can you please give me your solution it will useful to me. |
#13
|
|||
|
|||
Hey i already explained it:
Step 1: open onenote and take screenshot of the column you need from PDF file Step 2: The screenshot will appear in onenote. Right click the image and select option "copy text from picture" Step 3: open excel and paste it. You can also paste it in onenote |
#14
|
|||
|
|||
Hi,
What you really need to is have the File That was converted from PDF be active and the have a code that will loop thru it to copy the data from it to your destination workbook. Here is a sample dcode that you can use. You can add a line of code that will make sure that the workbook that you need the data from is Active. This code only move column "D" "Tag NR". You can modify it for the rest of the required data. Code:
Sub Test_Process() 'You need to have Original file converted Active to run this code. I only have it set to copy "Tag NR" Application.ScreenUpdating = False Dim lrow As Long '' PDF workbook Your Example File lrow = ThisWorkbook.Sheets("Data").Range("D18").End(xlUp).Row Dim llrow As Long With ActiveWorkbook llrow = ActiveSheet.Range("D18").End(xlUp).Row ''Active workbook Original File i = 7 Do While i <> llrow + 1 ThisWorkbook.Sheets("Data").Range("E" & lrow).Value = ActiveSheet.Range("D" & i).Text i = i + 1 lrow = lrow + 1 Loop End With Application.ScreenUpdating = True End Sub |
Tags |
excel vba acceleration |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract data based on pattern with respect to specific column | PRA007 | Excel Programming | 14 | 12-04-2015 04:32 AM |
Macro to highlight repeated words in word file and extract into excel file | aabri | Word VBA | 1 | 06-14-2015 07:20 AM |
How do I extract all my email addresses into one file? | JohnFurter | Outlook | 3 | 05-16-2015 04:32 AM |
Extract phone number from word file | donlincolnmsof | Word VBA | 12 | 06-19-2012 05:21 PM |
Extract Video from .ppsx file | designer | PowerPoint | 1 | 10-14-2011 08:00 AM |