Consulting

Results 1 to 7 of 7

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

  1. #1

    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

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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]

  3. #3
    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?

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

  5. #5
    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.

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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]

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •