Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access export to multiple sheets in Excel
Every month I'd like to export a large table (200,000 rows, 55 fields)
from an Access 97 database to a series of about 30 Excel workbooks with 10 sheets in each workbook. Every row in the table will go into one of the worksheets. New workbooks will be created every month. Obviously I'd like this to be as automatic as possible. The data will be split between workbooks based on fields called "Company" and "Product". The split between sheets within the workbook will be based on 10 "Regions". However, some combinations of these variables do not exist (and this changes monthly) and I would prefer that blank worksheets aren't created. I am thinking of using the OutPutTo command in VBA. From what I can tell if I want to export to a worksheet with a specific name the query (or table) from which I export has to have that name. If so do I need to use CreateQueryDef to create a new query with the correct name and criteria in it or is there a better solution? Assuming I use the approach outlined above I wasn't sure how to get Access to know exactly which of the 300 potential queries need to be run. I can easily create a query that will contain one row for each of the worksheets that I want to create but wasn't sure make use of this in the query Thanks for any help and sorry if I've been unclear! Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
export sheets to multiple new files | Excel Discussion (Misc queries) | |||
export multiple sheets to multiple excel files | Excel Discussion (Misc queries) | |||
Export Data from many workbooks with many sheets to Access | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Export multiple sheets to 1 csv file. | Excel Programming |