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
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
