File location

[JoeExcelHelp]

This is making me nuts for that past hour
cant figure out what I'm doing worng
The path is correct but it keeps telling me to check spelling or location
Did I miss something in terms of code?.. Help :(

Code: Select all

sPath = "Y:\Operations Planning\__Dashboards by RP\Airports TimeManagement Daily Hourly Performance\Data Inventory\Airport_Hours_Report_Data_Inventory.xlsx"

Code: Select all

Sub ImportActuals()
Dim sPath As String
Dim sFile As String
Dim wbD As Workbook
Dim shD As Worksheet
Dim rgD As Range
Dim wbS As Workbook
Dim shS As Worksheet
Dim rgS As Range

    Application.ScreenUpdating = False
    
    sPath = "Y:\Operations Planning\__Dashboards by RP\Airports TimeManagement Daily Hourly Performance\Data Inventory\Airport_Hours_Report_Data_Inventory.xlsx"
    If sPath = "False" Then Exit Sub
    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Actuals")
    Set rgD = Intersect(shD.Range("G1").CurrentRegion, shD.Range("G1").CurrentRegion.Offset(3, 1))
    Workbooks.Open sPath, UpdateLinks:=False
    Set wbS = ActiveWorkbook
    Set shS = wbS.Sheets(1)
    Set rgS = shS.Range("A1").CurrentRegion
    sFile = "'[" & Split(sPath, "\")(UBound(Split(sPath, "\"))) & "]" & shS.Name & "'!"
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C22," & sFile & "C2,""=""&RC7," & sFile & "C7,""=""&R1C," & sFile & "C5,""=""&R3C," & sFile & "C3,""=""&R2C," & sFile & "C6,""=""&""ALL"")"
    rgD.Value = rgD.Value
    wbS.Close False
    Application.ScreenUpdating = True
End Sub

[HansV]

What happens if you move Airport_Hours_Report_Data_Inventory.xlsx to a folder with a much shorter path, and modify the code accordingly?

[JoeExcelHelp]

That worked Hans.. Thank You.. Still very confusing why the original didnt.. strange

[HansV]

I suspect that the combination of path and filename was too long. (Or there was a typo in the path, but you mentioned that it was correct)