Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Red face Unanswered: How do you export 3 tables in Access to 3 worksheets in 1 Excel workbook?

    I have 3 tables in Access namely qryOutput, qryGCB & qryFL. I would like to export them into 1 workbook named QryJun07.xls w/c contains 3 worksheets: Output, GCB & FL. Is there a way to do this?
    Futhermore, I would also like to complete the records found in qryFL based from qryOutput w/c is the table that had all the complete records needed. qryFL contains some of the records but I need the complete version. How can this be done? I have a little background on VB, other than that I am totally lost! I need help ASAP.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Download the code under excel automation here:
    http://www.dbforums.com/showthread.php?t=1605962

    Run it, see if it has potential for the sort of thing you are looking for (I am 99% sure the answer is yes ).

    Not really very clear on the second question but let's stick to question one for now and move on once you have that nailed down.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Posts
    6

    Exclamation I get a warning sign when I open your program???

    Something to do w/ the Jet? I don't understand, why... when my database engine is pretty much up to date?!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope... sorry. I can't picture it in my noggin no matter how hard I try.

    Would you mind awfully posting the actual warning you receive?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Posts
    6

    Question It's OK now, found a workaround...

    Banks & their Security Settings! So I used my other workstation after everything has been put to order in the new office. & Yeah, your code works like a charm... I'm using the ExcelAutomation_V3 though instead of the one I 1st tried for the MS Office 2003 since this PC is using the MS Office 2000 version.

    Now, about the 2nd question I had....
    It's not basic formatting though. The Output tab contains all the accounts we have, what I want is to transfer all its rows of records that have blank cells for fields/headers "Client" to "State" to the FL tab. Another problem is since qryFL is incomplete I also have to copy all the rows of records we need to settle from the Output tab to the FL tab. Is there a way to save me from all this hassle in Access? Someone told me that one can determine what records to put in a certain table in Access based on certain parameters. I have a table of records we do not settle for Florida based on the list of Client IDs w/c changes per year that dictates what records shouldn't be found in qryFL & a constant list of account nos. that should be found in the FL tab but other than that all the records found in qryOutput/Output tab must be carried over to qryFL/FL tab.

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    Elixia

    Can't you just 'complete' these records in a query prior to exporting them?

    And instead of automating Excel why not use TransferSpreadsheet?
    Last edited by norie; 07-15-07 at 16:40.

  7. #7
    Join Date
    Jun 2007
    Posts
    6

    Lightbulb Thanks for the suggestion, Norie...

    I tried it (queries) but it doesn't solve the whole problem....
    The constant list of acceptable account nos. that should be found in the FL tab, from what I have observed are all 500 & up & from the pattern I've seen are all divisible by 5 since they all end w/ either a 0 or a 5. Maybe you can tell me how to make this parameter into a suitable query criteria?

    As for the reason why I opted to automate Excel, is because of the amount of formatting I keep on doing before I can make a single output workbook that I need to send via e-mail for further processing. These formatting as aforementioned are for all the rows of records that I need w/c have blank cells for fields "Client" to "State" <<Namely: (Client, Code, Dept, Acct, Key, Credit Ctr, Debit Ctr, Year, State...other fileds w/c are not blank ex. FL ctr)>> because I need to complete the blank cells by copying Code from FL Code, Acct & Key from FL acct & FL key, Credit Ctr from FL ctr, Year from filter results of Acct then Dept & Debit Ctr from filter results of Credit Ctr; then finally delete all 9021033 accounts while maintaining that the other complete rows of records are not affected.

  8. #8
    Join Date
    Mar 2006
    Posts
    163
    So why can't you fill in these blanks in Access?

  9. #9
    Join Date
    Jun 2007
    Posts
    6

    Post Coz the records are too many...

    & besides in Access you can't filter multiple fields at the same time, can you? By the way, you haven't told me how to make the parameters of acceptable account nos. into a query criteria???

Posting Permissions

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