Error from converting PDF to Excel files

[BBCC0000]

Hi all, I am currently trying to create a macro that helps me convert about 150 PDF files in a folder to Excel files in another folder. Below is my code:

Sub PDF_To_Excel()

Dim ws As Worksheet, pdf_path As String, excel_path As String
Set ws = ThisWorkbook.Sheets("Sheet 1")
pdf_path = "D:\Test\PDF"
excel_path = "D:\Test\Excel"

Dim fso As New FileSystemObject, fo As Folder, f As File, wa As Object, doc As Object, wr As Object, nwb As Workbook, nsh As Worksheet

Set fo = fso.GetFolder(pdf_path)
Set wa = CreateObject("word.application")
wa.Visible = False
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
nwb.SaveAs (excel_path & Replace(f.Name, ".pdf", ".xlsx"))
doc.Close
nwb.Close
Next
wa.Quit
End If

End Sub

After running this macro, the error either is "Error 400"or "Runtime Error '1004': Application-defined or Object-defined error".

Anyone has any idea to debug this? I have tried looking for few days but still can't figure it out.

Thanks in advance.

 

[Dan_W]

That error usually occurs when using the clipboard, and it's telling you that VBA is moving too quickly for Excel and yhe clipboard to keep up. In those situations, a solution is to pause between the Copy and paste commands.how long you set pause for depends - on how fast your computer is versus how much data it's dealing with. If you're dealing with PDF tiles of 150 pages, then it might require more than just a few seconds.

Sub Pause(Optional ByVal Period As Single = 2)
Dim T as single
T= Timer + Period
Do
     DoEvents
Loop While T > Timer 
End Sub

I would suggest putting this routine in the same module as your code, at the very bottom.

It might also make sense to move the Copy command up 2 lines and replace it with the pause coand, so those few lines could now look like:

wr.WholeStory
wr.Copy
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
Pause 4.  'This pauses the code for 4 seconds approx.
nsh.Paste

 

[Dan_W]

But you may want to consider a different approach altogether. You can avoid using the clipboard if you were to just save the PDF file you've opened with your code as a text file straight away. It all depends on what you plan on doing with the text.

Hope that helps

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Error from converting PDF to Excel files
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.