CODEWITHSUNDEEP
vbaexcelcsvdialog
jstevens13
jstevens13

Reputation: 1245

excel vba open dialog to import csv

I have this vba code below that was generated by the macro recorder. It imports a csv file into the current excel sheet with some specific column settings. Right now the path to the csv file is hard coded to "C:\Users\myuser\Desktop\logexportdata.csv". How can I change this so that there is a dialog prompt that asks the user to find the .csv file for import? 

Sub Import_log()

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\myuser\Desktop\logexportdata.csv", Destination:=Range( _
    "$A$2"))
    .Name = "logexportdata"
    .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 = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub

Upvotes: 6

Views: 15706

Answers (1)

user2140173
user2140173

Reputation:

try this:

Sub Import_log()

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & getFile, Destination:=Range( _
    "$A$2"))
    .Name = "logexportdata"
    .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 = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub


Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:="Csv (*.CSV), *.CSV", Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function

Upvotes: 8

Related Questions