Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all files in folder automatically using VBA
Hi-
I have a folder with multiple excel files in it and currently have a macro that goes through each file and pulls certain data into Access. The problem I have is that this folder is dynamic. The names and the number of files varies from month to month. The way my macro works now is it opens a specific file....runs the macro....and then closes the file. I have about 60 individual instructions for each of the files. Open specific file.....call Macro....close file.....open next file....call Macro.....close file.....etc. My question is this: Is there a way...using VBA...to point to a specific folder and run a macro for EVERY file in that folder?? I don't want to tell Excel what file to open....I just want it to open every file in the folder....run this macro against each file....and then close the files. Anyway this can be done or do I have to be specific in what I tell Excel to open?? Thanks in advance!! -Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all files in folder automatically using VBA
Thanks for the help William....I appreciate it!
I do have one problem with the code. I am getting a "Run-time error '9': Subscript out of range" error when I try to run this code. Here is the code: Sub openfilesInALocation() Dim i As Integer, wb As Workbook With Application.FileSearch ..NewSearch ..LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest" ..SearchSubFolders = False ..Filename = "*.xls" ..Execute For i = 1 To .FoundFiles.Count 'Open each workbook Set wb = Workbooks.Open(Filename:=.FoundFiles(i)) 'Perform the operation on the open workbook ----------- Errors on the following line: wb.Worksheets("sheet1").Range("A1").Select 'Save and close the workbook wb.Save wb.Close 'On to the next workbook Next i End With End Sub If anyone can solve this for me I would appreciate the help! -Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all files in folder automatically using VBA
Maybe you don't have a worksheet named "sheet1" for each of those workbooks.
If you wanted the leftmost worksheet, maybe you could use: wb.Worksheets(1).Range("A1").Select wrote: Thanks for the help William....I appreciate it! I do have one problem with the code. I am getting a "Run-time error '9': Subscript out of range" error when I try to run this code. Here is the code: Sub openfilesInALocation() Dim i As Integer, wb As Workbook With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest" .SearchSubFolders = False .Filename = "*.xls" .Execute For i = 1 To .FoundFiles.Count 'Open each workbook Set wb = Workbooks.Open(Filename:=.FoundFiles(i)) 'Perform the operation on the open workbook ----------- Errors on the following line: wb.Worksheets("sheet1").Range("A1").Select 'Save and close the workbook wb.Save wb.Close 'On to the next workbook Next i End With End Sub If anyone can solve this for me I would appreciate the help! -Chris -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all files in folder automatically using VBA
Just to add to Dave's point....
If you want to actually select a cell within the workbook opened (which is unnecessary 99% of the time), you should select the worksheet first and then the cell, so.... --------------- wb.Worksheets(1).Select wb.Worksheets(1).Range("A1").Select --------------- -- XL2003 Regards William "Dave Peterson" wrote in message ... Maybe you don't have a worksheet named "sheet1" for each of those workbooks. If you wanted the leftmost worksheet, maybe you could use: wb.Worksheets(1).Range("A1").Select wrote: Thanks for the help William....I appreciate it! I do have one problem with the code. I am getting a "Run-time error '9': Subscript out of range" error when I try to run this code. Here is the code: Sub openfilesInALocation() Dim i As Integer, wb As Workbook With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest" .SearchSubFolders = False .Filename = "*.xls" .Execute For i = 1 To .FoundFiles.Count 'Open each workbook Set wb = Workbooks.Open(Filename:=.FoundFiles(i)) 'Perform the operation on the open workbook ----------- Errors on the following line: wb.Worksheets("sheet1").Range("A1").Select 'Save and close the workbook wb.Save wb.Close 'On to the next workbook Next i End With End Sub If anyone can solve this for me I would appreciate the help! -Chris -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro creating folder and files automatically, without permission. | Excel Discussion (Misc queries) | |||
Open files in folder - skip if already open | Excel Programming | |||
Open all files in a folder | Excel Programming | |||
Open Excel files in a folder | Excel Programming | |||
open all files in a folder and ... | Excel Programming |