prompting the file selection

[lucabaz]

Hello, I'm trying to change the below code that has a fixed path and file name to import a csv file with the option to browse the file. Any help would be appreciated.

Sheets("Input").Select
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\LON\Active Forecast_27062024_06_50.csv" _
, Destination:=Range("$A$1"))

.Name = "Active_Forecast_LON_27062024_06_50_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

Sheets("Sold Rooms").Select
Range("F14").Select
ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
End With
Sheets("Instructions").Select
End Sub

 

[myall_blues]

Here's one way:

Dim TargetFile As String
    '
    ' Open dialogue box to browse for file
    '
    TargetFile = Application.GetOpenFilename("CSV files (*.csv), *.csv")
    '
    ' No file selected
    '
    If TargetFile = "False" Then
        '
        ' Display message box with an error and exit
        '
        MsgBox "No file selected.", vbExclamation, "Error"
        Exit Sub
    End If

Then in your code you would have:

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