I have several Excel workbooks with multiple pages in each, but, all have exactly the same column names and format. Is there a time saving way to import say all the sheets in a workbook at one time, instead of 1 worksheet at a time?
importing data
- Thread starter lipin
- Start date
- Local time
- Today, 13:35
- Joined
- Feb 28, 2001
- Messages
- 30,568
In theory, you can do this several ways, but each possible way depends on how much VBA code you can tolerate.
From your description, it sounds like the worksheets with the uniform format can be done inside a loop. As an overview, you would define a way to get the workbooks into a given directory before importing them. Move out anything that is a workbook that should NOT be imported.
Now use the FileSearch application to find all your .XLS workbooks as files.
OK, now open the Excel application as an application object.
For each file, start an outer loop that opens each file found by the FileSearch object. (There is a FileSearch.FilesFound.Count property you can use to control this loop, which can iterate over FileSearch.FilesFound(x) where x=1 to the count.)
Inside the outer loop, you can iterate over the workbook's Sheets collection. Again, there is a .Count property. You would iterate over Workbook.Worksheets
where n = 1 to the count of sheets, I think .Worksheets.Count. The name of each worksheet is Workbook.Worksheets.Name
From here, you can take one of two approaches.
1. Build a DoCmd with a TransferSheet action based on knowing the name of the worksheet you want.
2. Write an inner loop that reads each row of the Worksheet and stores a new record for each row.
In terms of simplicity, #1 is easier but has very limited ability to handle errors. Method #2 is harder to code but has limitless flexibility in error handling and format checking.
Don't forget to close each workbook when you are done with it.
To find out how to do these things,
FileSearch can be found in MS Access help as the FileSearch application.
The stuff about Excel can found in Excel help under the Visual Basic for Applications for Excel topic, which will lead you to a separate help file.
Both topics include examples that show code snippets that might be helpful in choosing and implementing your method.
From your description, it sounds like the worksheets with the uniform format can be done inside a loop. As an overview, you would define a way to get the workbooks into a given directory before importing them. Move out anything that is a workbook that should NOT be imported.
Now use the FileSearch application to find all your .XLS workbooks as files.
OK, now open the Excel application as an application object.
For each file, start an outer loop that opens each file found by the FileSearch object. (There is a FileSearch.FilesFound.Count property you can use to control this loop, which can iterate over FileSearch.FilesFound(x) where x=1 to the count.)
Inside the outer loop, you can iterate over the workbook's Sheets collection. Again, there is a .Count property. You would iterate over Workbook.Worksheets
where n = 1 to the count of sheets, I think .Worksheets.Count. The name of each worksheet is Workbook.Worksheets.NameFrom here, you can take one of two approaches.
1. Build a DoCmd with a TransferSheet action based on knowing the name of the worksheet you want.
2. Write an inner loop that reads each row of the Worksheet and stores a new record for each row.
In terms of simplicity, #1 is easier but has very limited ability to handle errors. Method #2 is harder to code but has limitless flexibility in error handling and format checking.
Don't forget to close each workbook when you are done with it.
To find out how to do these things,
FileSearch can be found in MS Access help as the FileSearch application.
The stuff about Excel can found in Excel help under the Visual Basic for Applications for Excel topic, which will lead you to a separate help file.
Both topics include examples that show code snippets that might be helpful in choosing and implementing your method.
