[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Opening file, alternative paths

Posted on 2014-07-17
6
Medium Priority
?
118 Views
Last Modified: 2014-07-17
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
0
Comment
Question by:Seamus2626
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40201573
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

0
 

Author Comment

by:Seamus2626
ID: 40201609
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
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40201623
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Accepted Solution

by:
duncanb7 earned 2000 total points
ID: 40201631
Ignore in last previous post, try this with directory check from vbDirectory at
http://www.techonthenet.com/excel/formulas/dir.php
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 ,vbDirectory) <> "" 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

0
 

Author Closing Comment

by:Seamus2626
ID: 40201990
Thanks Duncan!
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40201993
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question