VBA Pull Data from Closed Workbooks

J

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
840
Hello,

I have the following code which successfully pulls data from closed workbooks.

Is it possible to edit it so if it does not find a file stored in the folder then it ignores it?

Many thanks.


Code: 
Sub Forecast_Weekends()


    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    
    FD = Workbooks("Model.xlsm").Worksheets("Model").Range("Z1")
        
    cellValue1 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U6")
    cellValue2 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U12")
    cellValue3 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U13")
    cellValue4 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U19")
    cellValue5 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U20")
    cellValue6 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U26")
    cellValue7 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U27")
    cellValue8 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U33")
    
    fileSpec = FD & "Revenue\" & myValue3 & "\" & cellValue1 & ""
    fileSpec2 = FD & "Revenue\" & myValue3 & "\" & cellValue2 & ""
    fileSpec3 = FD & "Revenue\" & myValue3 & "\" & cellValue3 & ""
    fileSpec4 = FD & "Revenue\" & myValue3 & "\" & cellValue4 & ""
    fileSpec5 = FD & "Revenue\" & myValue3 & "\" & cellValue5 & ""
    fileSpec6 = FD & "Revenue\" & myValue3 & "\" & cellValue6 & ""
    fileSpec7 = FD & "Revenue\" & myValue3 & "\" & cellValue7 & ""
    fileSpec8 = FD & "Revenue\" & myValue3 & "\" & cellValue8 & ""


    Set destcell1 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C6")
    Set destcell2 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D6")
    Set destcell3 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E6")
    Set destcell4 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F6")
    Set destcell5 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G6")
    Set destcell6 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H6")
    Set destcell7 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I6")
    Set destcell8 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J6")
    Set destcell9 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K6")
    Set destcell10 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L6")
    Set destcell11 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M6")
    Set destcell12 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N6")
    Set destcell13 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C12")
    Set destcell14 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D12")
    Set destcell15 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E12")
    Set destcell16 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F12")
    Set destcell17 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G12")
    Set destcell18 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H12")
    Set destcell19 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I12")
    Set destcell20 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J12")
    Set destcell21 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K12")
    Set destcell22 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L12")
    Set destcell23 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M12")
    Set destcell24 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N12")
    Set destcell24 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C13")
    Set destcell26 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D13")
    Set destcell27 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E13")
    Set destcell28 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F13")
    Set destcell29 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G13")
    Set destcell30 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H13")
    Set destcell31 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I13")
    Set destcell32 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J13")
    Set destcell33 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K13")
    Set destcell34 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L13")
    Set destcell35 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M13")
    Set destcell36 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N13")
    Set destcell37 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C19")
    Set destcell38 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D19")
    Set destcell39 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E19")
    Set destcell40 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F19")
    Set destcell41 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G19")
    Set destcell42 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H19")
    Set destcell43 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I19")
    Set destcell44 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J19")
    Set destcell45 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K19")
    Set destcell46 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L19")
    Set destcell47 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M19")
    Set destcell48 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N19")
    Set destcell49 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C20")
    Set destcell50 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D20")
    Set destcell51 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E20")
    Set destcell52 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F20")
    Set destcell53 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G20")
    Set destcell54 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H20")
    Set destcell55 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I20")
    Set destcell56 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J20")
    Set destcell57 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K20")
    Set destcell58 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L20")
    Set destcell59 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M20")
    Set destcell60 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N20")
    Set destcell61 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C26")
    Set destcell62 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D26")
    Set destcell63 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E26")
    Set destcell64 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F26")
    Set destcell65 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G26")
    Set destcell66 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H26")
    Set destcell67 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I26")
    Set destcell68 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J26")
    Set destcell69 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K26")
    Set destcell70 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L26")
    Set destcell71 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M26")
    Set destcell72 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N26")
    Set destcell73 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C27")
    Set destcell74 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D27")
    Set destcell75 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E27")
    Set destcell76 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F27")
    Set destcell77 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G27")
    Set destcell78 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H27")
    Set destcell79 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I27")
    Set destcell80 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J27")
    Set destcell81 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K27")
    Set destcell82 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L27")
    Set destcell83 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M27")
    Set destcell84 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N27")
    Set destcell85 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C33")
    Set destcell86 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D33")
    Set destcell87 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E33")
    Set destcell88 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F33")
    Set destcell89 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G33")
    Set destcell90 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H33")
    Set destcell91 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I33")
    Set destcell92 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J33")
    Set destcell93 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K33")
    Set destcell94 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L33")
    Set destcell95 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M33")
    Set destcell96 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N33")
   
    r = 0
    
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    folderPath2 = Left(fileSpec, InStrRev(fileSpec2, "\"))
    folderPath3 = Left(fileSpec, InStrRev(fileSpec3, "\"))
    folderPath4 = Left(fileSpec, InStrRev(fileSpec4, "\"))
    folderPath5 = Left(fileSpec, InStrRev(fileSpec5, "\"))
    folderPath6 = Left(fileSpec, InStrRev(fileSpec6, "\"))
    folderPath7 = Left(fileSpec, InStrRev(fileSpec7, "\"))
    folderPath8 = Left(fileSpec, InStrRev(fileSpec8, "\"))


    fileName = Dir(fileSpec)
    fileName2 = Dir(fileSpec2)
    fileName3 = Dir(fileSpec3)
    fileName4 = Dir(fileSpec4)
    fileName5 = Dir(fileSpec5)
    fileName6 = Dir(fileSpec6)
    fileName7 = Dir(fileSpec7)
    fileName8 = Dir(fileSpec8)
        
    While Len(fileName) <> 0
        destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H12")
        destcell2.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H20")
        destcell3.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H28")
        destcell4.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H37")
        destcell5.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H48")
        destcell6.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H55")
        destcell7.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H64")
        destcell8.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H72")
        destcell9.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H80")
        destcell10.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H87")
        destcell11.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H96")
        destcell12.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H105")
        destcell13.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G12")
        destcell14.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G20")
        destcell15.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G28")
        destcell16.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G37")
        destcell17.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G48")
        destcell18.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G55")
        destcell19.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G64")
        destcell20.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G72")
        destcell21.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G80")
        destcell22.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G87")
        destcell23.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G96")
        destcell24.Offset(r, 0).Value = GetCellValue(folderPath2 & fileName2, "Forecast", "G105")
        destcell25.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H12")
        destcell26.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H20")
        destcell27.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H28")
        destcell28.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H37")
        destcell29.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H48")
        destcell30.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H55")
        destcell31.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H64")
        destcell32.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H72")
        destcell33.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H80")
        destcell34.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H87")
        destcell35.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H96")
        destcell36.Offset(r, 0).Value = GetCellValue(folderPath3 & fileName3, "Forecast", "H105")
        destcell37.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G12")
        destcell38.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G20")
        destcell39.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G28")
        destcell40.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G37")
        destcell41.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G48")
        destcell42.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G55")
        destcell43.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G64")
        destcell44.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G72")
        destcell45.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G80")
        destcell46.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G87")
        destcell47.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G96")
        destcell48.Offset(r, 0).Value = GetCellValue(folderPath4 & fileName4, "Forecast", "G105")
        destcell49.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H12")
        destcell50.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H20")
        destcell51.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H28")
        destcell52.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H37")
        destcell53.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H48")
        destcell54.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H55")
        destcell55.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H64")
        destcell56.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H72")
        destcell57.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H80")
        destcell58.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H87")
        destcell59.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H96")
        destcell60.Offset(r, 0).Value = GetCellValue(folderPath5 & fileName5, "Forecast", "H105")
        destcell61.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G12")
        destcell62.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G20")
        destcell63.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G28")
        destcell64.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G37")
        destcell65.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G48")
        destcell66.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G55")
        destcell67.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G64")
        destcell68.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G72")
        destcell69.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G80")
        destcell70.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G87")
        destcell71.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G96")
        destcell72.Offset(r, 0).Value = GetCellValue(folderPath6 & fileName6, "Forecast", "G105")
        destcell73.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H12")
        destcell74.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H20")
        destcell75.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H28")
        destcell76.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H37")
        destcell77.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H48")
        destcell78.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H55")
        destcell79.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H64")
        destcell80.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H72")
        destcell81.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H80")
        destcell82.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H87")
        destcell83.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H96")
        destcell84.Offset(r, 0).Value = GetCellValue(folderPath7 & fileName7, "Forecast", "H105")
        destcell85.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G12")
        destcell86.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G20")
        destcell87.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G28")
        destcell88.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G37")
        destcell89.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G48")
        destcell90.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G55")
        destcell91.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G64")
        destcell92.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G72")
        destcell93.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G80")
        destcell94.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G87")
        destcell95.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G96")
        destcell96.Offset(r, 0).Value = GetCellValue(folderPath8 & fileName8, "Forecast", "G105")
        
        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)
    folderPath3 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName3 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath4 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName4 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath5 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName5 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath6 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName6 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath7 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName7 = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
    folderPath8 = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName8 = 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)
    arg = "'" & folderPath3 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath4 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath5 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath6 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath7 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    arg = "'" & folderPath8 & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)


    Debug.Print arg
             
    GetCellValue = ExecuteExcel4Macro(arg)
    
End Function
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hello,

I started to try and shrink your code as it is very verbose! HOwever I just haven't got the time. In direct answer to your question, you can check if a file exists using DIR. I put it in a function to return a boolean:

Code: 
Function CheckFileExists(sPath As String) As Boolean
    CheckFileExists = Dir(sPath) <> ""
End Function

Just send the full path of the file and it will return true if the file exists
 
Last edited:
Upvote 0
gallen said:
Hello,

I started to try and shrink your code as it is very verbose! HOwever I just haven't got the time. In direct answer to your question, you can check if a file exists using DIR. I put it in a function to return a boolean:

Code: 
Function CheckFileExists(sPath As String) As Boolean
    CheckFileExists = Dir(sPath) <> ""
End Function

Just send the full path of the file and it will return true if the file exists
Click to expand...


Thanks so I have put that at the end. How do I make it skip those if it doesn't find the file?

Many thanks.
 
Upvote 0
Code: 
If CheckFileExists("C:\YOURFILEPATH")= True Then
        'File exists so do what you need to do
Else
        'File doesn't exist so notify user...
End if
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
VBA excel worksheet2 out of range even though the cell is within the range and additional value created
Replies
4
Views
229
MARK858
MARK858
D
  • Question Question
Merge Workbooks from Multiple Folders Based on by reading Order Numbers in Excel file
Replies
1
Views
609
ExcelToDAX
E
O
  • Solved
Copy from workbook and dynamically filldown data
Replies
3
Views
760
Alex Blakenburg
A
O
  • Solved
Copy a named range to another workbook
Replies
3
Views
485
Zot
Z
J
  • Question Question
VBA Excel - Master workbook - drawing data from multiple workbook to make library.
Replies
1
Views
729
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top