Paste Method Failed on PDF to Excel

[rex759]

Hello,
The macro below copies and pastes a PDF into Excel. It’s been working well for most of 2022. As users started getting an updated version Of Adobe, I started to see errors. I was able to work around some of the issues but now stuck on this last part. The versions of Adobe that seem to be the problem start with 2021.001.20145 and later.

This is the code the macro halts on:

.Range("A" & LastRow).PasteSpecial Paste:="Text"

However, if I open the Module and step through (F8 or F5) it works as expected.
Things that I have tried adding before the With Worksheets(“sheet1”)

Worksheets(“Sheet1”).visible = true
Worksheets(“Sheet1”).select
Range(“A1”).select

Still no luck.

Any ideas is greatly appreciated

Full Code

Sub Test()
'This works with one PDF
Dim o As Variant
Dim myworksheet As Worksheet
Dim adobereaderpath As String
Dim pathandfilename As String

Application.ScreenUpdating = False

'Check if Path is loaded
If Worksheets("data").Range("A1") = "" Then
 
'User picks folder, path is loaded to Data tab
  On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
    Dim folderPath As String

    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False

    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            folderPath = .SelectedItems.Item(1)

        Else ' else dialog is cancelled
            MsgBox "You have press cancelled"
            folderPath = "NONE" ' when cancelled set blank as file path.
            Exit Sub
        End If
    End With
err:

ThisWorkbook.Sheets("Data").Range("A1") = folderPath & "\"

End If

Set pathCell = Worksheets("data").Range("A1")

adobereaderpath = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
pathandfilename1 = pathCell & "First.pdf"
 
'Check is file name exists
strfilename = pathandfilename1
strfileExists = Dir(strfilename)

If strfileExists = "" Then
    MsgBox ("First PDF could not be located. Review the folder, ensure the naming is correct and try again.")
    Worksheets("Data").Range("A1").ClearContents
    Exit Sub
End If

'Open PDF
o = Shell("" & adobereaderpath & " " & pathandfilename1 & "", 1)
 
Application.OnTime Now + TimeValue("00:00:03"), "FirstStep"


End Sub
Private Sub FirstStep()

SendKeys ("^a")
SendKeys ("^c")
 
Application.OnTime Now + TimeValue("00:00:03"), "SecondStep"
 
End Sub
 
Private Sub SecondStep()
 
Dim wkb As Workbook
Dim sht As Worksheet


Call Shell("TaskKill /F /IM AcroRd32.exe", vbHide)

Windows("Master.xlsm").Activate

With Worksheets("Sheet1")
    .Range("A1") = "A"
    LastRow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & LastRow).PasteSpecial Paste:="Text"
End With


End Sub

 

[alansidman]

Alternative to VBA for importing PDF

How to import PDF files into Excel | Becker

In this article, you’ll learn how to import a table from a PDF into Excel. Learn more Excel tips and tricks from Becker!

www.becker.com

 

[rex759]

Thank you for your feedback.

I have read through Power Query and it seems like a sure thing. My issue is out of everyone using my current macro, only a handful is having issues. I didn’t want to rewrite my code just for the few that cannot use it.