Path Returns must have been moved or deleted but it has not

[bdouglas1011]

I have a path that I know works because another macro uses it just fine, but now using the same code to point to the same path I get a sorry cant find c:\..... is it possible it was moved or renamed or deleted.

the code that works

Dim r As Integer, F As String, Directory As String
    Dim col As Long, row As Long, colLet As String
    
    'get integers for the cell reference in the "Folder Path Cell" on the template sheet
    col = wColNumber(colRegEx(Range("CK30").Text))
    row = CInt(rowRegEx(Range("CK30").Text))
    
    Directory = cells(row, col).Text
    If Right(Directory, 1) <> "\" Then Directory = Directory + "\"
    r = row + 1
    
    'Get Directory
    F = Dir(Directory)
    Do While F <> ""
        r = r + 1
        cells(r, col + 2) = F
        'Get next File
        F = Dir()
    Loop

Here is the same thing just refer to the actual path as path not directory

 'get integers for the cell reference in the "Folder Path Cell" on the template sheet
    col = wColNumber(colRegEx(Range("CK30").Text))
    row = CInt(rowRegEx(Range("CK30").Text))
    
    'Turns off screen updates to avoid flashing screen
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
        
    'saving the survey sheet object to a variable
    Set Surveys = ThisWorkbook.Sheets("Surveys")
    
    'setting the defined path for the survey file
    path = cells(row, col).Text
    If Right(path, 1) <> "\" Then path = path + "\"
    
    'setting up the generic name for unformatted surveys, "path\<Well Name> Surveys.csv"
    Dim unformatSvy As String: unformatSvy = path + Range("g8").Text + " Surveys.csv"
    
    'Find the file marked as the survey file then exit loop
    For i = row + 2 To row + 15
        If cells(i, col + 11).Value = "Yes" Then
            path = path + cells(i, col + 2).Text
            Exit For
        End If
    Next i
    
    'Open the generated survey file under the object stored by wBook, set the page object in the open book
    Set wBook = Application.Workbooks.Open(path)

It stop here and says the error is with the

Set wBook = Application.Workbooks.Open(path)

The path is still the same "C:\Users\Remote\Desktop\GENERATED_LOGS"

I am confused

please help

 

[Marc L]

Via a breakpoint on this codeline check at least the content of this path variable.​

And you can use the VBA Dir function to check if the path exists …​

 

[bdouglas1011]

When I use this it works

Set wBook = Workbooks.Open("C:\Users\Remote\Desktop\New folder (2)\GENERATED_LOGS\HAWK UNIT B001H_Survey.xls")

But not

wBook = Application.Workbooks.Open(path)

I am not sure how to accomplish what you mentioned .... the line that is not working works fine on another workbook but it has a different layout

 

[rlv01]

You should always have code to check the file path. Here's one way:

    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(Path) Then
            Set wBook = Application.Workbooks.Open(Path)
        Else
            MsgBox "File not found ('" & Path & "') "
            Exit Sub
        End If
    End With

 

[Marc L]

As the message is clear enough so the filepath is not correct …​

​

Select the codeline and hit F9 key, launch the VBA procedure and when it stops on the break point see the content via the VBE Locals window​

or add a MsgBox path codeline just before, whatever …​

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Folder path does not exist....but it does
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.