Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List files from Folder (Excel 2002 (xp) SP2)

    hey gang--

    I am writing a routine to import data from a text file into a template. I would like to invoke the Open dialog box (or something similar) to give my users a navigable interface. However, instead of opening the file they select, I need to be able to capture the name and path of the file to include in my import procedure. Would anyone have any suggestions on this.

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

    Re: List files from Folder (Excel 2002 (xp) SP2)

    Look up the GetOpenFilename method of the Application object. It will let the user select a file, but it won't open that file, just make the selected file name and path available.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List files from Folder (Excel 2002 (xp) SP2)

    Here is some sample code I use:

    <pre>Sub GetOpenFileNameExample2()
    Dim vFilename As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim lCount As Long
    sPath = "c:windowstemp"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("text files (*.txt),*.txt", _
    , "Please select the file(s) to import", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = 1 To UBound(vFilename)
    MsgBox vFilename(lCount)
    Next
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List files from Folder (Excel 2002 (xp) SP2)

    Thanks Hans and Jan for the input. I will try to implement your suggestions this afternoon.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List files from Folder (Excel 2002 (xp) SP2)

    Quick followup question: for the FileFilter argument, is it possible to specify only files with no extention? The file to be imported is actually an export from an AS400 system. By default, these files are exported without extentions. If need be, I can get an RPG programmer to make a change to include an extention on the exported file, but thought I'd try this route first.

    (Yes, it would be easier and far more effiecient to query the AS400 directly, but my boss won't let me do it that way-- <font color=red>DON'T GET ME STARTED!!</font color=red>)

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

    Re: List files from Folder (Excel 2002 (xp) SP2)

    I think the best you can do is to omit the FileFilter argument. This will make GetOpenFileName display ALL files, including those without extension. The old DOS notation *. to specify files without extension doesn't work.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List files from Folder (Excel 2002 (xp) SP2)

    I thought so, but it couldn't hurt to ask. Thanks again.

Posting Permissions

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