How do I force an Excel macro to ask me which file and directory?

I recorded an Import External Data macro that I wish to use on other files.
It works beautifully, but only on that one specfic file in that one
directory. I can get the Visual Basic code for that macro to where I can
edit it, but don't know what to substitute for the directory/file so that the
macro is forced to ask.

Drop this into a module and run 'DoSomething' as an example

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, F As Object
Set SA = CreateObject("Shell.application")
Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not F Is Nothing) Then
PickFolder = F.items.Item.Path
End If
Set F = Nothing
Set SA = Nothing
End Function

Sub DoSomething()
userfile = PickFolder(strStartDir)
If userfile = "" Then
MsgBox "Canceled"
End If

With Application.FileSearch
.SearchSubFolders = True
.NewSearch
.Filename = ".xls"
.LookIn = userfile
.FileType = msoFileTypeExcelWorkbooks
.Execute
ffc = .FoundFiles.Count
MsgBox ffc

End With
End Sub


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ramius" <[email protected]> wrote in message
news:[email protected]...
> I recorded an Import External Data macro that I wish to use on other
files.
> It works beautifully, but only on that one specfic file in that one
> directory. I can get the Visual Basic code for that macro to where I can
> edit it, but don't know what to substitute for the directory/file so that
the
> macro is forced to ask.

I've been looking through other posts and it looks like a lot of times it is
helpful when the code is included. I think the pertinent parts of the code
are as follows:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\My Documents\capture.txt", Destination:=Range("A1"))
.Name = "CAPTURE"

where "D:\My Documents\capture.txt" is the location/file is recorded the
macro with.

"Ramius" wrote:

> I recorded an Import External Data macro that I wish to use on other files.
> It works beautifully, but only on that one specfic file in that one
> directory. I can get the Visual Basic code for that macro to where I can
> edit it, but don't know what to substitute for the directory/file so that the
> macro is forced to ask.

I bet Ken wanted at least one extra line:

If userfile = "" Then
MsgBox "Canceled"
End If

to
If userfile = "" Then
MsgBox "Canceled"
Exit Sub
End If



Ken Wright wrote:
>
> Drop this into a module and run 'DoSomething' as an example
>
> Function PickFolder(strStartDir As Variant) As String
> Dim SA As Object, F As Object
> Set SA = CreateObject("Shell.application")
> Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
> If (Not F Is Nothing) Then
> PickFolder = F.items.Item.Path
> End If
> Set F = Nothing
> Set SA = Nothing
> End Function
>
> Sub DoSomething()
> userfile = PickFolder(strStartDir)
> If userfile = "" Then
> MsgBox "Canceled"
> End If
>
> With Application.FileSearch
> .SearchSubFolders = True
> .NewSearch
> .Filename = ".xls"
> .LookIn = userfile
> .FileType = msoFileTypeExcelWorkbooks
> .Execute
> ffc = .FoundFiles.Count
> MsgBox ffc
>
> End With
> End Sub
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Ramius" <[email protected]> wrote in message
> news:[email protected]...
> > I recorded an Import External Data macro that I wish to use on other
> files.
> > It works beautifully, but only on that one specfic file in that one
> > directory. I can get the Visual Basic code for that macro to where I can
> > edit it, but don't know what to substitute for the directory/file so that
> the
> > macro is forced to ask.

--

Dave Peterson

Dohhhhhhh - Cheers for the catch Dave :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I bet Ken wanted at least one extra line:
>
> If userfile = "" Then
> MsgBox "Canceled"
> End If
>
> to
> If userfile = "" Then
> MsgBox "Canceled"
> Exit Sub
> End If
>
>
>
> Ken Wright wrote:
> >
> > Drop this into a module and run 'DoSomething' as an example
> >
> > Function PickFolder(strStartDir As Variant) As String
> > Dim SA As Object, F As Object
> > Set SA = CreateObject("Shell.application")
> > Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
> > If (Not F Is Nothing) Then
> > PickFolder = F.items.Item.Path
> > End If
> > Set F = Nothing
> > Set SA = Nothing
> > End Function
> >
> > Sub DoSomething()
> > userfile = PickFolder(strStartDir)
> > If userfile = "" Then
> > MsgBox "Canceled"
> > End If
> >
> > With Application.FileSearch
> > .SearchSubFolders = True
> > .NewSearch
> > .Filename = ".xls"
> > .LookIn = userfile
> > .FileType = msoFileTypeExcelWorkbooks
> > .Execute
> > ffc = .FoundFiles.Count
> > MsgBox ffc
> >
> > End With
> > End Sub
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
--
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
--
> >
> > "Ramius" <[email protected]> wrote in message
> > news:[email protected]...
> > > I recorded an Import External Data macro that I wish to use on other
> > files.
> > > It works beautifully, but only on that one specfic file in that one
> > > directory. I can get the Visual Basic code for that macro to where I
can
> > > edit it, but don't know what to substitute for the directory/file so
that
> > the
> > > macro is forced to ask.
>
> --
>
> Dave Peterson