Solved: How to do repeated File-Open commands in alternate locations

I want to open a file for writing, but it might be stored in one of several different locations. I'd like a procedure to try each location in turn until either success or file-not-found message. How would I code that?

The tail end of the path to my file is Maps\mapPix\Pix1 and these are the paths I'd like to check.

C:\
C:\Documents and Settings\Administrator\Application Data\
C:\Documents and Settings\Administrator\Local Setting\ApplicationData
C:\Documents and Settings\Administrator\My Documents\
C:\Documents and Settings\Administrator\Desktop\

Thanks

You can use Dir(filespec)<>"" to check if the file exists. We would need to know the filename in the filespec rather than just the folder/path to give a specific reply.

If you just want to see if you can write to a path, this can be used.
[VBA]'http://cuinl.tripod.com/Tips/o-2.htm by Peter G. Aitken
Public Function IsPathWriteable(Path As String) As Boolean

' Returns True if path can be written to,
' False otherwise.
On Error Resume Next
Dim TempFileName As String, fn As Integer

TempFileName = MakeTempFileName(Path)
fn = FreeFile
Open TempFileName For Output As #fn

' no error - this path is writeable.
If Err = 0 Then
Close #fn
Kill TempFileName
IsPathWriteable = True
Else
IsPathWriteable = False
End If

End Function

Public Function MakeTempFileName(Path As String)

' Returns a filename (with path) that is not
' already in use in the indicated path. Name
' has the form path\1.tmp, path\2.tmp, etc.
' If path is blank then App.Path is used.
' Does not verify that path is valid.

Dim x As Integer, s As String

If Path = "" Then Path = Application.Path

' Be sure path ends with \.
If (Right(Path, 1) <> "\") Then Path = Path & "\"

x = 0
Do
x = x + 1
s = Path & x & ".tmp"
Loop Until Dir(s) = ""

MakeTempFileName = Path & x & ".tmp"

End Function[/VBA]

Thanks, Kenneth. That's close but it doesn't quite do what I want, which is to open a specific file which might be found in one of several supplied paths.

The procedures shown check if the path exists and if it can be written to, but it doesn't return the file. When you say, "We would need to know the filename in the filespec rather than just the folder/path to give a specific reply.", are you referring to something other than the filename shown in my post? "Pix1", or is there another name that's needed?

Pix1 is a folder. You need a file extension to tell Windows, and me, what type it might be. If it is a JPG for example, you would probably want to insert it rather than "open" it. Inserting to what extent and where would be the next question.

If Pix1 is an XLS file type for example, Pix1.xls, then a workbook open would be used. Of course one needs to see if it was opened already but that is easily done.

Ok, we'll make it Pix1.jpg, but that's not really what's eluding me. Your post reminded me that I don't have to actually open these jpg's at all because they're just going to be loaded into Frames using LoadPicture, so I don't really have to do anything with them except verify that they exist. But that's only after I guess where the user has the folder of jpg's squirreled away. After I get that then I'm good to go, so the code I'm looking for would loop through those paths I mentioned until it comes to one that leads to my picture, or fails to find it.

This could be made into a function to return the fileName to make it more easily used. Another method one might use would be the dictionary object method.

[VBA]Sub PathIfExists()
Dim fileName As String, foundFile As String, i As Integer
Dim Paths(1 To 5)

fileName = "Maps\mapPix\Pix1.jpg"

Paths(1) = "C:\" 'All must have trailing backslash.
Paths(2) = "C:\Documents and Settings\Administrator\Application Data\"
Paths(3) = "C:\Documents and Settings\Administrator\Local Setting\ApplicationData\"
Paths(4) = "C:\Documents and Settings\Administrator\My Documents\"
Paths(5) = "C:\Documents and Settings\Administrator\Desktop\"
foundFile = ""

For i = 1 To UBound(Paths)
If Dir(Paths(i) & fileName) <> "" Then
foundFile = Paths(i) & fileName
Exit For
End If
Next i

If foundFile <> "" Then
MsgBox foundFile, vbInformation, "Found the File"
Else
MsgBox foundFile, vbInformation, "File NOT found!"
End If
End Sub[/VBA]

That works great. Just what I was looking for. Thank you.