Ok Heres the deal, I have 100+ tables in a access db and i want to create a script that takes the content of those tables and exports it to an excel spreadsheet. right now im doing (this is only part of the code)
BUT this only takes the info and pastes it into a NEW sheet within my excel work book that i specify. And that wont fly because as i said there are over 100 tables and my excel runs out of space at 99 sheets. So if somone can please help me in the right direction in doing one of the following ...
1) Take all the sheets and consolidate them into one 1 excel spreadsheet (they all have the same format, ie same columns)
2) tell me how to get access to export data from multiple tables to 1 sheet only
If anyone knows how to do this please let me know I would highly appreciate any help anyone can send my way THX
durrr... you have 100 tables with the same format?
You should probably take a look at that...
anyways, you could use a UNION query:
etc.. with 100 tables, that's going to be tedious, you'd probably hvae to write a script to write your script hehehe... then again, as I mentioned, if you have 100 tables with EXACTLY the same format, you probably have a design issue.
yea I know, i didnt design the system and im still new to its symantics the reason for having 100 tables with the same format is because their sources of data all come from different queries that returns different data which happenens to be in the same format. In any case, lets say i do what you suggested, and write my SQL statement (an albeit long one) to gather all the data in all the tables, how do I then export it to ONE excel sheet? any Ideas? I know icould export to excel but is there any way to automate this so somone can just push a button, that button will take that info from our query and export it to excel?
so i broke all the tables down into chunks.. which kind of working.. some tables are in exactly the same format...but im seeing now that many tables dont have EXACTLY the same format as the ones in the begining so access doesnt like when i try and unionize tables that dont have exactly the same format which is understandable.... so there is no way to lump multiple tables with different formats to gether for an export ???
(dont ask why i want to do this.. it just needs to get done! Im starting to think its not possible... any one have any ideas?!)