Trying to Modify a macro that imports a file

[abhaysanan]

I recorded a macro that imports an external comma delimited file and then imports it into a new spreadsheet. When I recorded the macro, I imported one particular file. Now I was trying to create an add in that would do this and where the file can be any file depending on what the user chooses.

Original code of the macro is below:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;P:\clients\tradestak\20061024\HENDERSON\HENDERSON_101206_US_TEST.Henderson_Extract.ext" _
        , Destination:=Range("A1"))
        .Name = "HENDERSON_101206_US_TEST.Henderson_Extract"
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I modified the above by adding the following:

FilePath = Application.GetOpenFilename("Ext Files (*.ext), *.ext", True)
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;FilePath" _
        , Destination:=Range("A1"))

The problem I'm facing is changing the .Name part of the code. As you can see, "HENDERSON_101206_US_TEST.Henderson_Extract.ext" is my file name and I can't seem to just extract that portion of the file name. Also, when I just replaced the path name by my variable FilePath and kept .Name as the same and chose the same file, I had an error at the .Refresh BackgroundQuery:=False line. I don't have much clue as to what that even means.

Any help would be appreciated. Thanks!

 

[Norie]

Try this.

FilePath = Application.GetOpenFilename("Ext Files (*.ext), *.ext", True)
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FilePath _
        , Destination:=Range("A1"))

 

[abhaysanan]

That works!! Thanks Norie. Would you know if its possible to have a default path for the Application.GetOpenFilename function? I mean I want it to open a particular directory rather than my documents everytime. Thanks!