CODEWITHSUNDEEP
vbaexcel
F1990
F1990

Reputation: 637

Open file from explorer when importing csv (VBA)

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?

Upvotes: 0

Views: 1305

Answers (2)

Andrew Leach
Andrew Leach

Reputation: 12983

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"))

Upvotes: 1

skkakkar
skkakkar

Reputation: 2828

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

Upvotes: 0

Related Questions