I keep getting errors when opening PDF documents from Excel

[willevans]

I am trying to parse specific measurements contained in some 3500 PDF files so they can be graphed and trends can be analyzed.

The problem is that sometimes my code can loop runs 30 times before I get the "There was an error opening this document. This file cannont be found." from Adobe Reader. Other times my code only manages to loop once before I get the error message from Adobe Reader. I've added pauses to the code to give it time to open and copy, but that doesn't seem to be enough. I'm stuck on what I can do to make this run more reliably (fewer file not found errors from Adobe that interupt my code) and run faster.

Public SummaryFile, ADOBEPATH, FileName, MyFile, FilePath, TestStr, MyAll, _
ParsedFolder, ParsedFile, NewFile, MyFolder, CleanFile  As String
Public PrintRow, lastrow As Integer
Public Serial_Num, B6_Diameter, B6_Roundess, B6_Concentr, B6_Parallel _
, B5_Diameter, B5_Roundess, B5_Concentr, B5_Parallel, Z70_Perp_X4 _
, Z70_Runo_X4, Z70_Flatnes, Z58_Perp_X4, Z58_Runo_X4, Z58_Flatnes _
, X3X1_Para_Inc, X3X1_Para_Cro, X4X3_Para_Inc, X4X3_Para_Cro As Variant
Public c As Range
Public FSO As New FileSystemObject


Sub Import_HSG_Inspection()
Dim task
Dim StartFile As File


'Navigate to “Tools” > “Reference” as shown above, then
'select "Microsoft Scripting Runtime"


With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
End With


SummaryFile = ActiveWorkbook.Name           'record the name of the current worksheet
Sheets("HSG_Format_Converter").Select       'set the starting tab to prevent errors


ADOBEPATH = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe " 'space required after .exe
MyFolder = "C:\Users\Z300741\Desktop\HSG_CMM_Inspections\"                  'working directory
ParsedFolder = "C:\Users\Z300741\Desktop\Parsed_Files\"                     'set already parsed file location
MyFile = Dir(MyFolder & "\*.pdf")                                           'phrase to open just the PDF files
          
Do While MyFile <> ""


    Range("N20:Y1000").Clear                                'clear the search field in the spreadsheet to prevent confusion
    
    Serial_Num = Mid(MyFile, InStr(1, MyFile, "SN") + 2, 5) 'get the Serial Number from file name
    Serial_Num = Replace(Serial_Num, "_", "")
'    Application.Wait Now + TimeValue("00:00:1")     ' wait 4 secs for copy to take affect
       
    Dim task
[INDENT]Dim Wk As Workbook[/INDENT]
[INDENT]  [/INDENT]
[INDENT]task = Shell(ADOBEPATH & MyFolder & MyFile, vbNormalFocus)  'open PDF file[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Application.Wait Now + TimeValue("00:00:3")     ' wait 4 secs to finish loading[/INDENT]
[INDENT]SendKeys "^a", True                             ' send key to select all text[/INDENT]
[INDENT]Application.Wait Now + TimeValue("00:00:2")     ' wait 4 secs for 'select' to take affect[/INDENT]
[INDENT]SendKeys "^c"                                   ' send key to copy[/INDENT]
[INDENT]Application.Wait Now + TimeValue("00:00:2")     ' wait 4 secs for copy to take affect[/INDENT]
[INDENT]SendKeys "^q"                                   'send key to close pdf file[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Range("N1").Select[/INDENT]
[INDENT]ActiveSheet.Paste[/INDENT]
[INDENT]Application.CutCopyMode = False                             'clear the clipboard[/INDENT]
[INDENT]    [/INDENT]
[INDENT]Range("N1").CurrentRegion = Range("N1").CurrentRegion.Value 'Convert pasted content to just values[/INDENT]


       
    Call Find_HSS_Data                  'attain the data in the block of imported text
       
    Call Print_Data                     'put the data in the right location in the final table
    
'    On Error Resume Next
    Name MyFolder & MyFile As ParsedFolder & MyFile            'move files already parsed to the ParsedFile location
    
    Range("N1:Y2000").Clear             'clear the search field to reduce confusion errors


Loop


With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With


End Sub