How can I solve this error "microsoft excel is waiting for another application to complete an ole action"?

Question

I am trying to convert pdf files into excel files. And converter is working perfect, but when it gets big pdf with like 300 pages "microsoft excel is waiting for another application to complete an ole action" error appears.

Here is the code:

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

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
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

doc.Close False
nwb.Close False
Next

wa.Quit

MsgBox "Done"

End Sub

I've tried to do something with settings and it didn't help. Application.wait and Application.DisplayAlerts = False didn't help also.
Is there anything that I can do or these files just too big?

Answer 1

I have not tested this but here are som ideas....

Working with large PDF files and converting them to Excel can be resource-intensive, and it seems like you're encountering issues with the current approach. Here are a few suggestions that might help:

Batch Processing: Instead of processing all 300 pages at once, consider processing the PDF in smaller chunks or pages at a time. You can modify your loop to handle a specific range of pages in each iteration.

For Each f In fo.Files
    Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")

    Dim startPage As Integer
    Dim endPage As Integer
    Dim pageStep As Integer
    startPage = 1
    pageStep = 50  ' Adjust this based on your needs
'doc.ComputeStatistics(2) returns the total number of pages in the PDF document 
    Do While startPage <= doc.ComputeStatistics(2)
        endPage = startPage + pageStep - 1
        If endPage > doc.ComputeStatistics(2) Then
            endPage = doc.ComputeStatistics(2)
        End If

        Set wr = doc.Range(doc.GoTo(What:=1, Which:=1, Count:=startPage), doc.GoTo(What:=1, Which:=1, Count:=endPage))
        Set nwb = Workbooks.Add
        Set nsh = nwb.Sheets(1)
        wr.Copy
        nsh.Paste

        nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", "_Pages_" & startPage & "-" & endPage & ".xlsx"))

        nwb.Close False
        startPage = endPage + 1
    Loop

    doc.Close False
Next

Memory Management: Large PDF files can consume a lot of memory. Ensure that you release objects and manage memory properly. Set objects to Nothing when you're done with them.

Set doc = Nothing
Set wr = Nothing
Set nwb = Nothing
Set nsh = Nothing

Error Handling: Implement proper error handling to identify the exact location of the problem. Add On Error Resume Next and check for errors after key statements.

On Error Resume Next
' Your code here
If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description
End If
On Error GoTo 0  ' Reset error handling