Results 1 to 11 of 11
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Locating a Filename and Path (Excel 2000)

    Darn it, this the sort of thing I'd look up in a search in no time. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    I'm looking for a method to find the the path and filename of a file without making the user type it in. I do not want to actually open the file.
    Ideally, I'd have the user browse to the file and select it. That info would be captured in a variable.

    Suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Use Application.GetOpenFilename. This lets the user browse to a file without actually opening it. It returns either the full path plus filename selected by the user, or False if the user canceled.

    Dim fileToOpen As Variant
    fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If fileToOpen <> False Then
    MsgBox "User selected " & fileToOpen
    End If

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Thanks Hans,
    you know I'm sure I asked the question before. Deja vu!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Using the fso is probably the best way, but you could also use the older FileSearch method, like this one that lists them all:

    Sub FindFiles()
    Dim lngCounter As Long
    With Application.FileSearch
    .NewSearch
    .LookIn = Application.DefaultFilePath
    .Filename = msoFileTypeExcelWorkbooks
    .SearchSubFolders = True
    If .Execute > 0 Then
    For lngCounter = 1 To .FoundFiles.Count
    ThisWorkbook.Worksheets("File List").Cells(lngCounter Mod Application.Rows.Count, _
    1 + Int(lngCounter / Application.Rows.Count)).Value = .FoundFiles(lngCounter)
    Next lngCounter
    Else
    MsgBox "No files found", vbInformation
    End If
    End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7DS

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Can you tell me more about the File System Object? I've tried to use it before without much success.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Locating a Filename and Path (Excel 2000)

    I can't tell you too much, I have used only some very elementary code, mostly from the VB/VBA Forum, or from VB & VBA in a Nutshell, Paul Lomax, O'Reilly Press , which is usually where I look when I want to review the FSO (the Methods are in alphabetical order, so the FSO section is together). Here are some URLs to look at:

    http://www.wdvl.com/Authoring/ASP/FSO/

    http://msdn.microsoft.com/library/default....eProcessing.asp

    It's shame Search is disabled, because you would get good Search hits in the VB/VBA Forum. And many of the -real- coders out here can probably give you an FSO method to find a file (as you noted, my answer didn't get to your question.)
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Isn't there also some stuff available in the current Windows Scripting Host help files? HTH
    Gre

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locating a Filename and Path (Excel 2000)

    How would you extract the file name only from this?

    thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locating a Filename and Path (Excel 2000)

    There are several ways to extract the file name. In Office 2000 ands up, you can use the Split function to split a full path into parts delimited by backslashes. The last part will be the file name:

    Dim fileToOpen As Variant
    Dim strArr() As String
    fileToOpen = Application.GetOpenFilename("Excel Workbooks (*.xls),*.xls")
    If fileToOpen <> False Then
    strArr = Split(fileToOpen, "")
    fileToOpen = strArr(UBound(strArr))
    MsgBox fileToOpen
    End If

    Another method would be to create a FileSystemObject object (need a reference to Windows Scripting Host for this). FileSystemObject has handy methods such as GetFileName, GetDriveName, GetExtension etc.

    Dim fileToOpen As Variant
    Dim fso As New FileSystemObject
    fileToOpen = Application.GetOpenFilename("Access Databases (*.mdb),*.mdb")
    If fileToOpen <> False Then
    fileToOpen = fso.GetFileName(fileToOpen)
    MsgBox fileToOpen
    End If
    Set fso = Nothing

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locating a Filename and Path (Excel 2000)

    I would do it like this:

    <pre>Dim vPath As Variant, strFile As String
    vPath = Application.GetOpenFilename("Excel Workbooks (*.xls),*.xls")
    If vPath <> False Then
    strFile = Dir(vPath)
    MsgBox strFile
    End If
    </pre>

    Legare Coleman

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Locating a Filename and Path (Excel 2000)

    Thanks guys!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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