Pulling Data - If cannot find file

[jamescooper]

Just need a bit of extra code to the below that says if the file is not found/doesnt exist then continue on and ignore, any ideas please?

Many thanks.

Public Sub Copy_Cells()


    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
    cellValue2 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U12")
      
    fileSpec = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""
    fileSpec2 = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue2 & ""
 
    Set destcell1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("C6")
    Set destcell2 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("D6")
    Set destcell3 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("C12")
    
    r = 0
    
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    folderPath2 = Left(fileSpec, InStrRev(fileSpec2, "\"))
    fileName = Dir(fileSpec)
    fileName2 = Dir(fileSpec2)
    While Len(fileName) <> 0
        destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H12")
        destcell2.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H16")
        destcell3.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G12")
        r = r + 1
        fileName = Dir
    Wend
      
End Sub


Private Function GetCellValue(ByVal workbookFullName As String, sheetName As String, cellsRange As String)


    Dim folderPath As String, fileName As String
    Dim arg As String
    
    folderPath = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath2 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName2 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    arg = "'" & folderPath & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath2 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    Debug.Print arg
          
    'Execute Excel 4 Macro with argument to closed workbook
    
    GetCellValue = ExecuteExcel4Macro(arg)
    
End Function

 

[jamescooper]

Any ideas?

I need an IFERROR statement somewhere I believe?