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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 01:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com