PDF Word & Excel VBA

jayped

New Member
Joined
Mar 20, 2019
Messages
45
Hi

I have two VBA macros that convert PDF to Word. Both have parts which supply my needs and parts which don't. Therefore, I would like to combine both according to my needs. The first one allows me to convert multiple PDF files in a folder while the other is only set up to convert one file at a time. The first one converts the PDF to Word then to Excel while the other converts from PDF to Word OR Excel depending on what I want the extension to be. In the first one, some files are converted perfectly while others aren't. The second one converts to Word perfectly but columns are merged when converting to excel which I do not want. The second VBA connects to Adobe Acrobat which I believe to be ideal when converting to Word (this way columns align perfectly). I want to take the feature from VBA 1 which allows me to convert multiple files in a folder and place in VBA 2 which connects to Acrobat and then convert to excel, or take the Acrobat elements and place into VBA 1 so that the Word conversion is perfect before converting to Excel.

I found the codes below online and did not create them myself. I have basic understanding of VBA and I've failed to combine the two to help myself.
VBA 1:
Option Explicit


Sub PDF_To_Excel()

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Setting")

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

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set wa = CreateObject("word.application")

wa.Visible = True

Dim nwb As Workbook
Dim nsh As Worksheet

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


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy

nsh.Paste
Columns("A:E").ColumnWidth = 30
Range("D1:D500").Replace What:=" BBD", Replacement:=""
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))
Application.CutCopyMode = False

doc.Close True
nwb.Close True
Next

wa.Quit
Set doc = Nothing
Set wa = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Conversion Complete!"

End Sub


VBA 2:

Option Explicit

Sub convert_pdf_doc()

Dim aApp As Acrobat.AcroApp
Dim av_doc As CAcroAVDoc
Dim pdf_doc As CAcroPDDoc
Dim jso_obj As Object

Dim sfile As String
Dim dfile As String
Dim ext As String

ext = "doc"

sfile = "C:\Users\jpedro004\Desktop\Credit Card Statements\PDF Files\G Mahon - August 2020.pdf"
dfile = Replace(sfile, ".pdf", "." & ext, 1)

Set aApp = CreateObject("AcroExch.App")
Set av_doc = CreateObject("AcroExch.AVDoc")

If av_doc.Open(sfile, vbNull) = True Then

Set pdf_doc = av_doc.GetPDDoc
Set jso_obj = pdf_doc.GetJSObject

jso_obj.SaveAs dfile, "com.adobe.acrobat." & ext

End If

av_doc.Close False

aApp.Exit
Set aApp = Nothing
Set av_doc = Nothing

End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,114,016
Messages
5,545,514
Members
410,689
Latest member
ConfuzzledThomas
Top