Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Directory Access (97 SR2)

    Is there any way to make a listbox display the contents of a given directory?

    If so, is there a way to open a spreadsheet based on the item selected in the list box?

    Any help Greatly Appreciated!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Directory Access (97 SR2)

    Sir GIF Genius,
    This will list all files in a directory. This is adapted from the Excel help.
    <pre>Private Sub UserForm_Initialize()
    Dim MyPath, MyName

    MyPath = "c:"
    MyName = Dir(MyPath, vbDirectory)

    Do While MyName <> ""
    If MyName <> "." And MyName <> ".." Then
    If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
    lstSelect.AddItem MyName
    End If
    End If
    MyName = Dir
    Loop
    End Sub
    </pre>


  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Directory Access (97 SR2)

    Works Great!

    Now, how might I open the returned file?

    Also, how can I create a directory in VBA?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Directory Access (97 SR2)

    The listbox contains a list of directories, not files. Therefore, if you open what is returned, you will be opening a directory not a file.

    It sounds like you want to use the GetOpenFilename method.
    Legare Coleman

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Directory Access (97 SR2)

    You can build an array of filenames and using the ListIndex property open the selected file. The following code does this using the Filesystem Object of the Scripting Runtime, and is placed in the Userform module.<pre>Dim oFso As FileSystemObject
    Dim oFldr As Folder
    Dim oFile As File
    Dim iFiles As Integer
    Dim FileList() As String
    Dim WorkPath As String

    Private Sub UserForm_Initialize()
    Dim I As Integer
    WorkPath = "C:My Documents"
    Set oFso = New FileSystemObject
    Set oFldr = oFso.GetFolder(WorkPath)
    iFiles = oFldr.Files.Count
    ReDim FileList(iFiles)
    For Each oFile In oFldr.Files
    FileList(I) = oFile.Name
    I = I + 1
    Next
    Me.ListBox1.List = FileList()
    Me.Label2 = WorkPath
    Me.Label3 = ""
    End Sub</pre>

    Include an Command Button with the caption Open and the following code :<pre>Private Sub CommandButton1_Click()
    If Me.Label3 = "" Then
    MsgBox "Please Select a File"
    Else
    Workbooks.Open Filename:=WorkPath & FileList(ListBox1.ListIndex)
    Unload Me ' Remove this line to allow opening of multiple files.
    End If
    End Sub</pre>

    I attach a demonstration sheet which you can try out. Double clicking on the filename will also cause it to open, by using the ListBox1_DblClick event. You will have to include a reference in your Workbbok to the Microsoft Scripting Runtime. Or you could populate the list box using the more standard VBA Dir() method.

    EDIT : Actually you do not need to use an array for your purposes as a List box is in effect an array. The attached file does not use a seperate array.

    Andrew C
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Directory Access (97 SR2)

    I just looked at the spreadsheet you attached. Just a simple question. How did you get the arrow and box? Are they part of Excel or a third party.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Directory Access (97 SR2)

    It is just Callout shape (Line Callout 2 I think) from the Autoshapes set. I just applied custon line formatting and a shadow (semi-transparent). You can access the Autoshapes via the Drawing toolbar.

    Andrew

Posting Permissions

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