This function will show the file dialog and return the selected file (if any)
or False if the user cancels:
Function ChooseFile() As String
Dim GotFile As Boolean, FileSpec As Variant, Response As Integer
GotFile = False
While Not GotFile
FileSpec = Application.GetOpenFilename("Text Files,*.txt", , "Select the
file to import:", "Load", False)
If (FileSpec = False) Then
Response = MsgBox("You need to specify a file: Press OK to try again
or Cancel to abort import", _
vbOKCancel, "MUST SELECT FILE:")
If Response = vbCancel Then GotFile = True
Else
GotFile = True
End If
Wend
ChooseFile = FileSpec
End Function

To use it in your code:
Dim CFile as Variant

Cfile = ChooseFile
If CFile = False Then
' write code here for case where user cancels; e.g.:
MsgBox "Import operation aborted", vbInformation, "USER CANCELLED"
Else
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & CFile,
Destination _
:=Range("A1"))
' Below finds just the file name, stripping off the rest of the path:
.Name = Right(CFile, Len(CFile) - InStrRev(CFile,"\"))
End If

--
- K Dales


"MWS" wrote:

I need to write a macro to import data from potentially several different
files and need it to be dynamic enough for the user to select the file they
need. Part of the code I originally used was:

' Keyboard Shortcut: Ctrl+r
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;W:\Pending_Oppty_Report\manager_rejected_010 32005_400a.txt",
Destination _
:=Range("A1"))
.Name = "manager_rejected_01032005_400a"

The issue I'm having is that this hard-code only imports data from the
"manager_rejected_01032005_400a" file and I need to have the macro allow the
user to select any file within the directory. I think I need to "pause" the
macro, allow the user to select a file and then resume the importing of the
data, but am having major problems in completely this - please help.

Any and All Help Is Appreciated - Thank You