Let user select file to open

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

Originally Posted by Richard Buttrey

Hi,

In much the same way as before but with an extra variable

Dim stFName As String, stfPath As String
stfPath = Application.GetOpenFilename
stFName = Left(StrReverse(Split(StrReverse(stfPath), "\")(0)), InStr(StrReverse(stfPath), "."))
Sheets("Data").Select

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

'etc...

Yes, I believe it should be this simple but my knowledge is very limited.
I still get an error though. "Application defined or object-defined error"

Dim stFName As String, stfPath As String
Sheets("Data").Select

'Hämta filnamnet utan extension
stfPath = Application.GetOpenFilename

'Get the name of the file without the file extension. (Make:data_267798_7267.csv become, data_267798_7267)
stFName = Left(StrReverse(Split(StrReverse(stfPath), "\")(0)), InStr(StrReverse(stfPath), "."))

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

Any thoughts?

There are some differences from the orignial 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"

Your code:

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

The parameter stfName = Returns only "data". Not data_267798_7267 so there is something wrong there as well.


Attachments
Here is the data file I am trying to import.
data_267798_7267.csv
Here is the xlsm file that imports the file. It is unfortunately in swedish. On the second slide There is a macro button that starts the import. (1.Hämta Data)
Orderhandling.zip