asked on

Opening file, alternative paths

Hi,

A different user has a slightly different path to a spreadsheet as another

User 1
"x:\Morris Info\ME_Morris_Project\ME Downloads\"
User 2
"x:\SPM\Morris Info\ME_Morris_Project\ME Downloads"

So i am using "Workbooks.Open Filename:= _"

Is there a line of code to say, if path "x:\Morris Info\ME_Morris_Project\ME Downloads\" not found, then try path

"x:\SPM\Morris Info\ME_Morris_Project\ME Downloads"

Many thanks

duncanb7

You need Dir() function on VBA to check the file path existing or not
before switching to other file path
Duncan

sub test()
Filename="example.xls"
localfile="x:\Morris Info\ME_Morris_Project\ME Downloads\"
localfile2="x:\SPM\ME_Morris_Project\ME Downloads\"
If Dir(localfile) <> "" Then
    MsgBox "File exists."
'Do your code here
 Workbooks.Open Filename:=localfile & "\"  & Filename
Else
    MsgBox "File doesn't exist."
'Do you cod here
Workbooks.Open Filename:= localfile2 & "\" & Filename

End If

End Sub

Open in new window

Seamus2626

ASKER

Thanks Duncan,

When running the code, its is skipping to

Else

Workbooks.Open Filename:=localfile2 & "\" & Filename

Whereas localfile is available, and should be opening, it is skipping to localfile2 which is not available from my PC!

Thanks

duncanb7

Try this
Duncan

sub test()
Filename="example.xls"
localfile="x:\Morris Info\ME_Morris_Project\ME Downloads\"
localfile2="x:\SPM\ME_Morris_Project\ME Downloads\"
If Dir(localfile & "\" & Filename) <> "" Then
    MsgBox "File exists."
'Do your code here
 Workbooks.Open Filename:=localfile & "\"  & Filename
Else
    MsgBox "File doesn't exist."
'Do you cod here
Workbooks.Open Filename:= localfile2 & "\" & Filename

End If

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

duncanb7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Thanks Duncan!

duncanb7

Thanks for your points

Have a nice day

Duncan