VBA code to convert PDF document into Excel friendly format.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Option Explicit
 
Sub PDF_To_Excel()
 
    Dim setting_sh As Worksheet
    Set setting_sh = ThisWorkbook.Sheets("Sheet1")
     
    Dim pdf_path As String
    Dim excel_path As String
     
    pdf_path = setting_sh.Range("E11").Value
    excel_path = setting_sh.Range("E12").Value
     
    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File
     
    Set fo = fso.GetFolder(pdf_path)
     
    Dim wa As Object
    Dim doc As Object
    Dim wr As Object
     
    Set wa = CreateObject("word.application")
     
    'Dim wa As New Word.Application
    wa.Visible = True
    'Dim doc As Word.Document
     
    Dim nwb As Workbook
    Dim nsh As Worksheet
    'Dim wr As Word.Range
     
    For Each f In fo.Files
    Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
    Set wr = doc.Paragraphs(1).Range
    wr.WholeStory
    wr.Copy
     
    Set nwb = Workbooks.Add
    Set nsh = nwb.Sheets(1)
     
     
    nsh.Paste
    nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))
     
    doc.Close False
    nwb.Close False
    Next
     
    wa.Quit
     
    MsgBox "Done"
 
End Sub
Source
https://www.pk-anexcelexpert.com/pdf-to-excel-converter-in-excel-vba/

Categorized in:

VBA Excel,

Last Update: May 13, 2024