Excel VBA: Use getOpenFilename to open folder AND files

Question

I want to use this routine Application.GetOpenFilename to open either a *.txt file OR a whole folder. Is this somehow possible?
E.g. if no file/folder is selected, the parent's folder path is returned, otherwise the selected filename?

Example: Let's assume I have a file called "test.txt" in the path C:\folder1\folder2\test.txt. Now I am lazy when searching files and select C:\folder1 (the "parent folder"). My program now searches within the subfolders for test.txt. But sometimes I am not lazy and I want to select the specific file test.txt

I am searching for one user friendly dialog to handle both: open a folder (and return the folder path only) and open a file (and return the file path)

What does opening a "whole folder" look like - do you mean you want to show the folder's contents in Windows Explorer, or you want to open all files in the folder? In any case, you cannot use getopenfilename to select a folder: if no file is selected then nothing at all is returned. If you want your user to be able to select a folder then you need to use a different route for that. — Tim Williams, Oct 22 '12 at 18:39
(I specified my question.) So `getopenfilename` is not able to achieve this. Is there another function? — vince_h, Oct 24 '12 at 12:01
I found a solution to my question. Perhaps not exactly what I searched, but anyway: [http://www.oaltd.co.uk/mvp/MVPPage.asp](http://www.oaltd.co.uk/mvp/MVPPage.asp) Jim's Toolbox "BrowseForFolder" should help you :-) — vince_h, Oct 24 '12 at 13:15

score 1 · Answer 1 · answered Oct 22 '12 at 14:08

1

By parent I assume you mean the file from which the VBA is called. If not you should be able to adjust the below pretty easilly.

Sub getFileorFolder()

fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If fileToOpen = False Then fileToOpen = ThisWorkbook.Path

MsgBox "File is " & fileToOpen

End Sub

answered Oct 22 '12 at 14:08

Scott Holtzman

27,099
5
37
72

1

@vince_h -> seriously? you are too lazy to click down one or two more folders to get a file? Or is there some other reason why you need to do this? – Scott Holtzman Oct 24 '12 at 13:04

score 0 · Answer 2 · answered Sep 02 '15 at 18:16

I have a better way of opening text files, but utilizing one of the answers above.

Sub ImportTextFile()
'better method to retrieving Data from txt.
If Not Range("A2").Value = "" Then
MsgBox "Clear Data First"
Sheets("Input DATA").Select
Exit Sub
End If

fileToOpen = application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen = False Then fileToOpen = ThisWorkbook.Path
MsgBox "File is " & fileToOpen

    With ActiveSheet.QueryTables.Add(connection:= _
        "TEXT;" + fileToOpen, Destination:=Range("$A$2"))
        '.name = "All"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Call RemoveEmptyRows
End Sub

Sub RemoveEmptyRows()
On Error Resume Next
Range("A2:A5000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Resume:
Range("A2").Select
End Sub

Excel VBA: Use getOpenFilename to open folder AND files

2 Answers2