Trying to Modify a macro that imports a file

abhaysanan

Board Regular
Joined
Jun 1, 2005
Messages
95
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:

Rich (BB code):
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:

Rich (BB code):
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!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
FilePath = Application.GetOpenFilename("Ext Files (*.ext), *.ext", True)
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FilePath _
        , Destination:=Range("A1"))
 

abhaysanan

Board Regular
Joined
Jun 1, 2005
Messages
95
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,727
Members
410,702
Latest member
clizama18
Top