Let user select file to open

Hello.

I have a script that imports a csv-file and delimit the inputs with colon.
Today the code is hard coded and I would like the user to be able to select which file to import.
How can I do this?

Current code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Me\Google Drive\Our team\data_267798_7267.csv", _
        Destination:=Range("$A$1"))
        .Name = "data_267798_7267"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .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, 1, _
        1, 1, 1, 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

Dim nwWbName As String, nwWb As Workbook

nwWbName = Application.GetOpenFilename
Set nwWb = Workbooks.Open(nwWbName)

I don't know how this can be applied to your code up top there as this will actually open the csv as its own workbook, which is what you were asking for.

Originally Posted by Richard Buttrey

Hi

Sub YourSub

Dim stFName as String
stFName = Application.GetOpenFileName

With ActiveSheet.QueryTables.Add(Connection:= _
        stFName, _
        Destination:=Range("$A$1"))
'....etc

It seems to work well until the ....etc code starts.
I have written this way:

Dim stFName As String
stFName = Application.GetOpenFilename
Sheets("Data").Select

With ActiveSheet.QueryTables.Add(Connection:= _
        stFName, _
        Destination:=Range("$A$1"))
        .Name = "data_267798_7267"        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False

What is marked in red text is causing a problem since it is hardcoded filename without the extension.
Can I extract the filenamn from the stFName in some way?

/Anders

In the original code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Me\Google Drive\Our team\data_267798_7267.csv", _
        Destination:=Range("$A$1"))
        .Name = "data_267798_7267"

How can I get the filename into the .data-field?
It need to be the same filename as the imported filename.

Anders