![]() |
|
#1
|
|||
|
|||
|
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 |