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_01032005_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

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" <[email protected]> wrote in message
news:[email protected]...
> 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_01032005_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

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_01032005_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

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_01032005_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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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_01032005_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
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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_01032005_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
>
>
>