You can prompt for a file like this:

Sub Demo()
Dim FName As Variant
FName = Application.GetOpenFilename("Text files(*.TXT),*.TXT")
If FName < False Then
MsgBox FName
Else
MsgBox "User cancelled"
End If
End Sub


--
Jim Rech
Excel MVP
"Katherine" wrote in message
...
|I need to import a series of comma delimited text files into Excel. The
| following chunk of code (pilfered from the macro recorder so it's probably
| more wordy than it needs to be) does what I want but it relies on the name
| and path of the text file to be hardcoded in.
|
| With ActiveSheet.QueryTables.Add(Connection:= _
| "TEXT;C:\Documents and Settings\admin\Desktop\Press Repor1.txt",
| Destination _
| :=Range("A2"))
| .Name = "Press Repor1_2"
| .FieldNames = True
| .RowNumbers = False
| .FillAdjacentFormulas = False
| .PreserveFormatting = True
| .RefreshOnFileOpen = False
| .RefreshStyle = xlInsertDeleteCells
| .SavePassword = False
| .SaveData = True
| .AdjustColumnWidth = False
| .RefreshPeriod = 0
| .TextFilePromptOnRefresh = False
| .TextFilePlatform = 850
| .TextFileStartRow = 1
| .TextFileParseType = xlDelimited
| .TextFileTextQualifier = xlTextQualifierDoubleQuote
| .TextFileTabDelimiter = True
| .TextFileCommaDelimiter = True
| .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,
| 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
|
| What I need is for the user to be able to browse to the text file to be
| imported, because it could be anywhere and called anything. Doing a
File|open
| and browsing to the text file means that the user would have to go through
| the text import wizard, and I don't trust them to choose the right
settings.
|
| Can anyone provide a suggestion as to how I can allow the user to browse
to
| the text file and then for excel to get the text import settings from my
code
| rather than forcing the user to choose them? It's probably really simple
but
| I'm drawing a massive blank.