Open file from explorer when importing csv (VBA)

Question

I have created a button that imports a specific .csv file into my excel sheet. However, I want to specify which file opens when clicking on the button. So if the button is pressed: Excel opens the explorer and the user can specify which file to open.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;Path\20150728.csv", _
        Destination:=Range("$A$1"))
    .Name = "Tasks-Job--1g2MZtgw-Feike_15min_data-20150728"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 932
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft

I tried to implement the following code:

Call Shell("explorer.exe" & " " & "Path", vbNormalFocus)

But I didn't manage to get it working correctly. Any suggestions?

See the question [here](http://stackoverflow.com/q/25153342/215576) for an example or the MSDN help article [here](https://msdn.microsoft.com/en-us/library/office/ff836226.aspx) (check the example). Bottom line, you don't need to shell out; there's a builtin function that opens the choose file dialog and allows you to get the user selected file or files. You can then save the selected file's path and use it instead of the hardcoded value you have on the `Connection`. — ssarabando, Aug 08 '15 at 09:02

score 1 · Answer 1 · answered Aug 08 '15 at 09:26

You can get Excel to allow you to choose your file with the FileOpen dialog:

With Application.FileDialog(msoFileDialogFilePicker)
    .Show
    strFileName = .SelectedItems(1)
End With

strFileName contains the complete path+filename of the selected file, which you should then be able to use with something like

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFileName, _
    Destination:=Range("$A$1"))

score 0 · Answer 2 · answered Aug 08 '15 at 09:10

Following code fragment allows to open explorer and enables choosing the file. But it is not opening the file and system hangs. It may help you in progressing further.

 Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, _
            ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub test()
    Dim Tskrfile

    Tskrfile = Application.GetOpenFilename()

    If Tskrfile <> False Then
        ShellExecute 0, vbNullString, Tskrfile, vbNullString, vbNullString, vbNormalFocus
    End If
End Sub

HTH

Open file from explorer when importing csv (VBA)

2 Answers2