Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Question Unanswered: VBA Access/Excel Problem

    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)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, RS("TableName").Value, RS("File").Value, False,

    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)

    OR

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    durrr... you have 100 tables with the same format?

    Why?

    You should probably take a look at that...

    anyways, you could use a UNION query:

    SELECT *
    FROM yourFirstTable
    UNION
    SELECT *
    FROM yourSecondTable
    UNION
    SELECT *
    FROM yourThirdTable

    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    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?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    union queries return a single dataset.

    You can export this dataset to a single sheet.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Posts
    20

    update....

    ok so i tried what you said, did SELECT * FROM TABLE_NAME UNION... and i got the wonderful error msg

    Query is too complex.

    HAHA....just my luck!! It was a good try and probably the best shot at getting what i wanted but i guess access cant handle that many unions.. any other ideas

  6. #6
    Join Date
    Jun 2004
    Posts
    20

    more update....

    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?!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •