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

R

Ramius

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.
 
K

Ken Wright

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
 
R

Ramius

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.
 
D

Dave Peterson

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
 
Top