browsing for a folder, opening file

am6019a · June 30th, 2006, 09:58 AM

Level of experience: What's a workbook

Setup: I am writing a macro to open a bunch of file folders and hunt out specific files

Problem: If the marco can't find the file I want it to let the user browse through the file folders to find the one it wants. I found this bit of code, but it doesn't let me see XL files and it doesn't open files for me. Can anyone see a way to augment it?

Code:

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260


Function BrowseFolder(Optional Caption As String, _
    Optional InitialFolder As String) As String

    Dim SH As Shell32.Shell
    Dim F As Shell32.Folder

    Set SH = New Shell32.Shell
    Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, _
        InitialFolder)

    If Not F Is Nothing Then
        BrowseFolder = F.Items.Item.Path
    End If

End Function
Sub browsefolderfunc()
Dim FName As String
    FName = BrowseFolder("Select a folder", "C:\")
    If FName = "" Then
        MsgBox "You didn't select a folder"
    Else
        MsgBox "You selected: " & FName
    End If
End Sub

BrianWren · June 30th, 2006, 01:39 PM

This is VB6? VBA behind Excel?

marcostraf · July 5th, 2006, 08:26 PM

the code you posted just displays the Select Folder dialog. Once you have the name of the folder, you can use the Dir function to get the files you want.
But more simply you can use the CommonDialog control
Please let us know if you need more input

ShaileshShinde · July 6th, 2006, 09:17 AM

Hi,

I have the same problem, Can u please tell me how to use the Dir function to get the files.

Also How to Process each file in folder.....Loop through the Files collection.

something like this?

Dim objFile
For Each objFile In objFolder.Files
MsgBox objFile.Name
Next

BrianWren · July 6th, 2006, 12:15 PM

To use the Dir function, you first invoke it with arguments, and then repeatedly invoke it with no arguments. It returns just the name of the fileââno path info. After the last file, it returns an empty string. If it is invoked again after that it raises an error.

Code:

    Dim FilNm As String

    FilNm = Dir("C:\Temp\*.tmp")

    Do Until FilNm = ""
        ' Call any processing you want to do here:
        ProcessMyFile FilNm
        FilNm = Dir()
    Loop

In the process described above your post ShaileshShinde, Folder objects are not being used (ultimately). The process laid out returns a string into FName.

If you use the Windows Scripting Runtime (FileSystemObject) the Dir() function is not the best approach.

MetalSQL · February 21st, 2007, 07:09 PM

Back to the original code, the answer is sort of right in front of you...

In the line of code that says:

Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, _
InitialFolder)

The BIF_RETURNONLYFSDIRS flag ... you guessed it ... returns only directories (folders). That's why your Excel files don't show up.

Change that flag to the other one listed up in the constants section, BIF_BROWSEINCLUDEFILES, and you should see both folders and files.