MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Directory Browser within a macro?


Posted by Daryl Searle on February 08, 2002 7:37 AM

I have written a macro that works in the following way.
I type the file name and path (of a chosen text file) into cell A1, I then click on a command button to run the macro and it opens the specified text file in Excel and manipulates it automatically.
Is there a way where I can click the command button and the macro opens some sort of directary browser where I can then select the text file and then return to the macro to continue manipulating the text.


Posted by XLmon on February 08, 2002 8:48 AM

Try something like this:

Sub OpenMultipleFiles()
Dim fn As Variant, i As Integer
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For i = 1 To UBound(fn)
Debug.Print "Selected file #" & i & ": " & fn(i)
Workbooks.Open fn(i), , ReadOnly
'put your code to manipulate the text here
ActiveWorkbook.Close
Next i
End Sub

Posted by DK on February 08, 2002 8:48 AM

How about this?

Sub SelectAFile()
Dim strFileName As String

strFileName = Application.GetOpenFilename("Text files,(*.txt)")

If strFileName = "" Then Exit Sub 'User pressed cancel

End Sub

Regards,
D