Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Hello,
I want to be able to run the same macro on say all the excel files in a given folder. (without me having to select each file). I want to be able to select the appropriate folder(that contains all the excel files) and the macro should do the rest. What is the best way to do that ? Please help. Thanks Srikanth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Dim sPath as String, sName as String
Dim bk as Workbook sPath = "C:\Myfolder\" sName = Dir(sPath & "*.xls") do while sName < "" set bk = workbooks.open(sPath & sName) . . . process bk bk.close Savechanges:=False ' or true sName = Dir Loop -- Regards, Tom Ogilvy "Srikanth" wrote in message om... Hello, I want to be able to run the same macro on say all the excel files in a given folder. (without me having to select each file). I want to be able to select the appropriate folder(that contains all the excel files) and the macro should do the rest. What is the best way to do that ? Please help. Thanks Srikanth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Hi, Tom:
In my experience, if, between calls to Dir$, you make any changes to the directory, like saving a file, it gets "mixed up" and may skip a file or process one twice. This may not be a problem if you are only opening the file, then closing it without resaving. To avoid any problems, I usually use a first Do/Loop to get all of the file names into an array, then a 2nd For/Next loop to retrieve the names from the array and process the files. I do this often enough that I wrote a sub to get the file names, so my code would look like this: Dim sPath as String Dim sName as String Dim bk as Workbook Dim FileList() As String Dim i As Long Dim n As Long sPath = "C:\Myfolder\" sName = "*.xls" n = GetFileList(sPath & sName, FileList()) For i = 1 to n 'NB: loop won't execute if n = 0 set bk = workbooks.open(sPath & FileList(i)) 'process bk Next i ~~~~~~~~~~~~~~~~~~~~~ Function GetFileList(Pattern As String, FileNames() As String) As Long Dim f As String Dim n As Integer n = 0 Erase FileNames() f = Dir$(Pattern) Do While Len(f) n = n + 1 ReDim Preserve FileNames(1 To n) As String FileNames(n) = f f = Dir$() Loop GetFileList = n End Function 'GetFileList On Mon, 30 Aug 2004 16:06:26 -0400, "Tom Ogilvy" wrote: Dim sPath as String, sName as String Dim bk as Workbook sPath = "C:\Myfolder\" sName = Dir(sPath & "*.xls") do while sName < "" set bk = workbooks.open(sPath & sName) . . . process bk bk.close Savechanges:=False ' or true sName = Dir Loop |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
I am aware of this as well, although it has never caused me any problems
that I recall. Normally I write the array approach, but then it gets ignored because someone posts code like I did (which is simpler), so I figured if the user had problems, they would post back. the KB article on this only cites xl2000. So have you truly experienced problems or you do you just believe that it could and avoid them? -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... Hi, Tom: In my experience, if, between calls to Dir$, you make any changes to the directory, like saving a file, it gets "mixed up" and may skip a file or process one twice. This may not be a problem if you are only opening the file, then closing it without resaving. To avoid any problems, I usually use a first Do/Loop to get all of the file names into an array, then a 2nd For/Next loop to retrieve the names from the array and process the files. I do this often enough that I wrote a sub to get the file names, so my code would look like this: Dim sPath as String Dim sName as String Dim bk as Workbook Dim FileList() As String Dim i As Long Dim n As Long sPath = "C:\Myfolder\" sName = "*.xls" n = GetFileList(sPath & sName, FileList()) For i = 1 to n 'NB: loop won't execute if n = 0 set bk = workbooks.open(sPath & FileList(i)) 'process bk Next i ~~~~~~~~~~~~~~~~~~~~~ Function GetFileList(Pattern As String, FileNames() As String) As Long Dim f As String Dim n As Integer n = 0 Erase FileNames() f = Dir$(Pattern) Do While Len(f) n = n + 1 ReDim Preserve FileNames(1 To n) As String FileNames(n) = f f = Dir$() Loop GetFileList = n End Function 'GetFileList On Mon, 30 Aug 2004 16:06:26 -0400, "Tom Ogilvy" wrote: Dim sPath as String, sName as String Dim bk as Workbook sPath = "C:\Myfolder\" sName = Dir(sPath & "*.xls") do while sName < "" set bk = workbooks.open(sPath & sName) . . . process bk bk.close Savechanges:=False ' or true sName = Dir Loop |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Hi Tom and Myrna, I still have a problem. When you define sName = "*.xls" it seems to search for a file with a name *.xls instead of using the * as a wildcard. Please help. Srikanth *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Not for me:
from the immediate window: ? dir("c:\data\*.xls") KZ081-Default Survey1.xls or spath = "C:\data\" sname = Dir(spath & "*.xls") ? sname KZ081-Default Survey1.xls -- Regards, Tom Ogilvy "Srikanth Ganesan" wrote in message ... Hi Tom and Myrna, I still have a problem. When you define sName = "*.xls" it seems to search for a file with a name *.xls instead of using the * as a wildcard. Please help. Srikanth *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Or (obviously!) for me, either.
On Mon, 30 Aug 2004 22:27:16 -0400, "Tom Ogilvy" wrote: Not for me: from the immediate window: ? dir("c:\data\*.xls") KZ081-Default Survey1.xls or spath = "C:\data\" sname = Dir(spath & "*.xls") ? sname KZ081-Default Survey1.xls |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening multiple files from within a macro
Myrna....
I've looked at your code...relatively simple. I've also tried understanding Bob Philips code in thread: User Input to open multiple files - much more difficult for me to understand. my particular needs are as follows: opening several csv files from multiple folders. one file at a time, I need to modify each file and then close each file as an excel file (preferably in the same location as the csv file). (Ultimately, I could move all the files into one folder as a temporary work-around, but this wouldn't be my preference) By the way, do you have any recommendations for any particular resources for gaining ground in VBA (particularly for excel)? Thank you. "Myrna Larson" wrote in message ... Hi, Tom: In my experience, if, between calls to Dir$, you make any changes to the directory, like saving a file, it gets "mixed up" and may skip a file or process one twice. This may not be a problem if you are only opening the file, then closing it without resaving. To avoid any problems, I usually use a first Do/Loop to get all of the file names into an array, then a 2nd For/Next loop to retrieve the names from the array and process the files. I do this often enough that I wrote a sub to get the file names, so my code would look like this: Dim sPath as String Dim sName as String Dim bk as Workbook Dim FileList() As String Dim i As Long Dim n As Long sPath = "C:\Myfolder\" sName = "*.xls" n = GetFileList(sPath & sName, FileList()) For i = 1 to n 'NB: loop won't execute if n = 0 set bk = workbooks.open(sPath & FileList(i)) 'process bk Next i ~~~~~~~~~~~~~~~~~~~~~ Function GetFileList(Pattern As String, FileNames() As String) As Long Dim f As String Dim n As Integer n = 0 Erase FileNames() f = Dir$(Pattern) Do While Len(f) n = n + 1 ReDim Preserve FileNames(1 To n) As String FileNames(n) = f f = Dir$() Loop GetFileList = n End Function 'GetFileList On Mon, 30 Aug 2004 16:06:26 -0400, "Tom Ogilvy" wrote: Dim sPath as String, sName as String Dim bk as Workbook sPath = "C:\Myfolder\" sName = Dir(sPath & "*.xls") do while sName < "" set bk = workbooks.open(sPath & sName) . . . process bk bk.close Savechanges:=False ' or true sName = Dir Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening multiple files into one workbook, but multiple worksheets.... | Excel Discussion (Misc queries) | |||
opening multiple .txt files from multiple folders | Excel Discussion (Misc queries) | |||
How can I view files chronologically when opening multiple files | Excel Discussion (Misc queries) | |||
Opening multiple .xls files with a macro | Excel Programming | |||
opening multiple .csv files from the web | Excel Programming |