Loop not opening PDFs...so copy/Paste to Excel fails (I think)

Question

Apologies in advance for the length of this post, but I wanted to describe my issues in detail in the hopes one of you VBA masters can assist

Goal

1. Loop through all PDFs in a folder
2. For each PDF:select all/copy/paste into Excel
3. Call a separate macro to convert the pasted data into something legible.

Background The below sub [CopyPDFtoExcel()] worked yesterday but is now failing on the ActiveSheet.Paste line with the

"Runtime error '1004' Paste method of Worksheet class failed".

If I step though (via F8), it appears to NOT be actually opening the PDF, and therefor is unable to select all/copy/paste, producing the Runtime error. However, I do not get an error dialog, which I would think I would get (from the Debug.Assert False) if it can't find the file.

My fName's are defined as variable via a named range called path2008. These file paths were derived by running PullFilePathsforPDFs(), which spits out the full file path for each PDF in my folder. Then, I have selected those file paths and given it a name, in this case path2008, which is for 13 different PDFs. NOTE: There are actually 250+ PDFs in this folder but I selected a subset for testing, hence the 13 associated with path2008.

What I have done so far

1. Tested the file path for each PDF in the path2008 range by using the (cumbersome non-looping) ActiveWorkbook.FollowHyperlink method, which successfully opens all the PDFs. So, I'm pretty confident the file paths are correct.

   'ActiveWorkbook.FollowHyperlink "file path here" 
   

2. Stripped out the select all/copy/paste VBA code, leaving just the loop [See the sub TroubleshootingOpeningPDFLoop()]. When I step through the FIRST time the yellow line goes from the Set oPDDoc = oAVDoc.GetPDDoc line to the End If....presumabley meaning it found a file during the first loop (though I do not see the PDF open). On the SECOND (and all subsequent loops) it goes to Else then Debug.Assert False (but no error dialog appears).

3. Restarted Excel and Acrobat, same issue

4. Restarted computer, same issue

5. Recreated a new workbook, same issue

Main code

Sub CopyPDFtoExcel()
Dim fName       As Variant
Dim wbPayroll As Excel.Workbook
Dim wsConvert       As Excel.Worksheet
Dim oPDFApp     As AcroApp
Dim oAVDoc      As AcroAVDoc
Dim oPDDoc      As AcroPDDoc

Set wbPayroll = Workbooks("Payroll.xlsm")
Set wsConvert= wbPayroll.Sheets("Convert")

Set oPDFApp = CreateObject("AcroExch.App")
Set oAVDoc = CreateObject("AcroExch.AVDoc")
Set oPDDoc = CreateObject("AcroExch.PDDoc")

'Open the PDF file. The AcroAVDoc.Open function returns a true/false 
For Each fName In Range("path2008")
If oAVDoc.Open(fName.Text, "") = True Then
    Set oPDDoc = oAVDoc.GetPDDoc
Else
    Debug.Assert False
End If

'Copy all using Acrobat menu
oPDFApp.MenuItemExecute ("SelectAll")
oPDFApp.MenuItemExecute ("Copy")

'Paste into Convert sheet
wbPayroll.Activate
wsConvert.Cells(1, 1).Select
ActiveSheet.Paste 'It worked yesterday, but now error on this line with below error
'Runtime error '1004' Paste method of Worksheet class failed

oAVDoc.Close (1)    '(1)=Do not save changes
'oPDDoc.Close
Call ConversionMacro
Next

'Clean up
Set wbTransfer = Nothing
Set wsNew = Nothing
Set oPDFApp = Nothing
Set oAVDoc = Nothing
Set oPDDoc = Nothing
End Sub

My effort to isolate the PDF open failure problem

Sub TroubleshootingOpeningPDFLoop()
Dim fName       As Variant
Dim wbPayroll As Excel.Workbook
Dim wsConvert       As Excel.Worksheet
Dim oPDFApp     As AcroApp
Dim oAVDoc      As AcroAVDoc
Dim oPDDoc      As AcroPDDoc

'Define your spreadsheet
Set wbPayroll = Workbooks("Payroll.xlsm")
Set wsConvert= wbPayroll.Sheets("Convert")

'Instantiate Acrobat Objects
Set oPDFApp = CreateObject("AcroExch.App")
Set oAVDoc = CreateObject("AcroExch.AVDoc")
Set oPDDoc = CreateObject("AcroExch.PDDoc")

'Open the PDF file. The AcroAVDoc.Open function returns a true/false 
For Each fName In Range("path2008")
If oAVDoc.Open(fName.Text, "") = True Then
    Set oPDDoc = oAVDoc.GetPDDoc
Else
    Debug.Assert False
End If
Next
End Sub

Sub used to pull the file paths

Sub PullFilePathsforPDFs()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Long
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("D:\Stuff\MoreStuff") 'all PDFs I need are stored here
i = 1
For Each objFile In objFolder.Files
Cells(i + 1, 1) = objFile.Name
Cells(i + 1, 2) = objFile.Path
i = i + 1
Next objFile
End Sub

Answer 1

I am having cut/copy - paste troubles lately (runtime error 1004) Using the latest Excel but also working on "ancient" applications. What did the trick for me was working with the original "given" name (Sheet1,Sheet2 etc. As soon as I added/renamed the same sheets, the runtime errors came back.

Answer 2

If you want to make sure to generate an error when the opening operation does not succeed, I would add the following at the end of TroubleshootingOpeningPDFLoop:

If oPDDoc is nothing then
    Debug.Assert False
End If

If this doesn't return an error, that means that the file is open in the application, but that it is not visible. It could be caused by the fact that you are using a PDDoc instead of an AVDoc. So, switching the 2 might allow you to see it when debugging.

As of your main problem, it might be due to the fact that Acrobat does not process the commands fast enough and you need to include some waiting time in your code to let Acrobat enough time to process the command. For example, you could have:

'Copy all using Acrobat menu
oPDFApp.MenuItemExecute ("SelectAll")
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
oPDFApp.MenuItemExecute ("Copy")

Which will make VBA wait one second before running the next command.