Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2017, 03:00 AM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default 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...
Attached Files
File Type: pdf ORIGINAL FILE.pdf (168.9 KB, 10 views)
File Type: pdf SAMPLE FILE EXTRACT TO EXCEL.pdf (135.7 KB, 15 views)
File Type: xlsm PDF TO EXCEL.xlsm (11.4 KB, 11 views)
Reply With Quote
  #2  
Old 06-08-2017, 11:20 AM
charlesdh charlesdh is offline Extract column from Pdf to Excel file Windows 7 32bit Extract column from Pdf to Excel file Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

If you can not edit the PDF file then I do not think you will be able to do as you want.
Reply With Quote
  #3  
Old 06-08-2017, 11:44 AM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
Hi,

If you can not edit the PDF file then I do not think you will be able to do as you want.
HI charlesdh,

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
Reply With Quote
  #4  
Old 06-08-2017, 12:04 PM
charlesdh charlesdh is offline Extract column from Pdf to Excel file Windows 7 32bit Extract column from Pdf to Excel file Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 06-08-2017, 12:45 PM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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.
Ok then thanks for the reply
Reply With Quote
  #6  
Old 06-08-2017, 12:54 PM
charlesdh charlesdh is offline Extract column from Pdf to Excel file Windows 7 32bit Extract column from Pdf to Excel file Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
When you extract the PDF to excel you can post it here and I or someone my be able to help.
Reply With Quote
  #7  
Old 06-08-2017, 01:53 PM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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
When you extract the PDF to excel you can post it here and I or someone my be able to help.

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.
Reply With Quote
  #8  
Old 06-09-2017, 01:29 AM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

Can you upload some sample file which you import to excel might be i can have some idea about to convert file
Reply With Quote
  #9  
Old 06-09-2017, 01:04 PM
charlesdh charlesdh is offline Extract column from Pdf to Excel file Windows 7 32bit Extract column from Pdf to Excel file Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 06-10-2017, 12:10 PM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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"
Hi charlesdh,

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.
Attached Files
File Type: xlsx ORIGINAL FILE converted from NITROPDF.xlsx (9.8 KB, 8 views)
File Type: docx ORIGINAL FILE TO WORD FIRST.docx (121.6 KB, 6 views)
File Type: xlsm FROM WORD TO EXCELTEST.xlsm (19.7 KB, 9 views)
Reply With Quote
  #11  
Old 06-10-2017, 07:32 PM
ferocious12 ferocious12 is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
 
Join Date: Jan 2017
Posts: 6
ferocious12 is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 06-11-2017, 12:04 AM
sathishsusa sathishsusa is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
Extract column from Pdf to Excel file
 
Join Date: May 2017
Posts: 10
sathishsusa is on a distinguished road
Default

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.
Reply With Quote
  #13  
Old 06-11-2017, 04:58 PM
ferocious12 ferocious12 is offline Extract column from Pdf to Excel file Windows 7 64bit Extract column from Pdf to Excel file Office 2016
Novice
 
Join Date: Jan 2017
Posts: 6
ferocious12 is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 06-11-2017, 06:25 PM
charlesdh charlesdh is offline Extract column from Pdf to Excel file Windows 7 32bit Extract column from Pdf to Excel file Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
Reply

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
Extract column from Pdf to Excel file How do I extract all my email addresses into one file? JohnFurter Outlook 3 05-16-2015 04:32 AM
Extract column from Pdf to Excel file Extract phone number from word file donlincolnmsof Word VBA 12 06-19-2012 05:21 PM
Extract column from Pdf to Excel file Extract Video from .ppsx file designer PowerPoint 1 10-14-2011 08:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft