macro for importing external data

[Tom]

I used the record macro function to create the following macro for importing
a text file into Excel 2007. How can I edit the macro to allow the selection
of the file location and type?

Here is the macro that the record macro function created:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Recipe Converter Work\Tom-01.rcp",
Destination:=Range("$A$1" _
))
.Name = "Tom-01"
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

This portion of the macro will break down the text file into two columns.
The deliminator is a :.

Any suggestions on how to change this so that I can reterive the necessary
files from anywhere?

Tom

 

[Joel]

Sub test()

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("Cannot OPen File - exiting Macro")
Exit Sub
End If
'Original filename is commented out, use Dialog Box name instead
'FName = "E:\Recipe Converter Work\Tom-01.rcp"
FName = fileToOpen
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FName, _
Destination:=Range("$A$1"))
.Name = "Tom-01"
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub