Importing Data Via A Macro
 

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

Importing Data Via A Macro
 

Dim fName as Variant
Dim fName1 as String
fName = Applicaton.GetOpenFileName(Filefilter:= _
"Text Files (*.txt),*.txt")
if fName = False then
msgbox "Nothing selected"
exit sub
End if
fName1 = Dir(fName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=Range("A1"))
.Name = left(fName1,len(fName1)-4)

End With

--
Regards,
Tom Ogilvy


"MWS" wrote in message
...
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

Importing Data Via A Macro
 

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

Importing Data Via A Macro
 

Thank You - I Will Try You Suggestion

"K Dales" wrote:

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

Importing Data Via A Macro
 

Thank You - I Will Try You Suggestion

"Tom Ogilvy" wrote:

Dim fName as Variant
Dim fName1 as String
fName = Applicaton.GetOpenFileName(Filefilter:= _
"Text Files (*.txt),*.txt")
if fName = False then
msgbox "Nothing selected"
exit sub
End if
fName1 = Dir(fName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=Range("A1"))
.Name = left(fName1,len(fName1)-4)

End With

--
Regards,
Tom Ogilvy


"MWS" wrote in message
...
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

Importing Data Via A Macro
 

Tom, I tried to attach the code to a command button (CommandButton1) and
received the following error:

Run-time error '424':
Object required

the code is as follows and halts at the "fName = Application......." line:

Private Sub CommandButton1_Click()
Dim fName As Variant
Dim fName1 As String
fName = Applicaton.GetOpenFilename(Filefilter:= _
"Text Files (*.txt),*.txt")
If fName = False Then
MsgBox "Nothing selected"
Exit Sub
End If
fName1 = Dir(fName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=Range("A1"))
.Name = Left(fName1, Len(fName1) - 4)

End With

Any Assistance Will Be Appreciated - Thank You

"Tom Ogilvy" wrote:

Dim fName as Variant
Dim fName1 as String
fName = Applicaton.GetOpenFileName(Filefilter:= _
"Text Files (*.txt),*.txt")
if fName = False then
msgbox "Nothing selected"
exit sub
End if
fName1 = Dir(fName)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=Range("A1"))
.Name = left(fName1,len(fName1)-4)

End With

--
Regards,
Tom Ogilvy


"MWS" wrote in message
...
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