Results 1 to 8 of 8
  1. #1
    Kristy is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    7

    Export 3 queries from access to excel in 1 workbook but each query on a separate tab

    Hi, I am a copy and paste existing code to get what I need person. I can't write code and can barely understand the basics of it. I prefer to use the old fashion macro's in access - the Macro button -(I am using the way back 2003 version). I can create queries, tables, command buttons but that's about it. Like I said, I try and copy exiting VB code when it needs to be replicated.



    My problem is that no current code exists for my issue here at work. I need 1 macro to create an excel workbook (this I have done and it works) but I need it to drop those 3 queries to separate tab (with query name) in that 1 workbook.

    Query is set and criteria won't change - I can edit it when/if needed.

    Query 1 - Agency Checklist
    Query 2 - Producer Report
    Query 3 - License Report

    Each query has separate field names. That seemed to be a question asked often..

    Right now I am using 3 separate macros to run each report independently, copying the data from the Producer Report spreadsheet and License Report spreadsheet and pasting the data into the Agency Checklist Workbook. While it works, I know there is a way to have the macro drop each query into 1 excel workbook.


    Any assistance you can offer is greatly appreciated. -- side note, I searched the forum and found some questions pertaining to this but I don't think the code was what I was needing...it may be but I may not know enough to realize it as well.

    Please go easy on my brain, you could consider me a child when it comes to access vb code.

    Kristy

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    Kristy is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    7
    Thank you Alan, this is a little much for me. I get it but I don't know how to apply it to have the other queries drop. I can transfer 1 query to the excel but the current data isn't overwritten - another problem. I am working in the actual Macro not in the VB code too. I don't know if that's the issue or not. Here is a shot of what I am working with.
    [IMG]file:///C:\Users\KSTARL~1\AppData\Local\Temp\msohtml1\01\c lip_image002.jpg[/IMG][IMG]file:///C:\Users\KSTARL~1\AppData\Local\Temp\msohtml1\01\c lip_image002.jpg[/IMG]

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    This worked for me, even when the help states it should not?
    Code:
    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8,"Transactions","C:\temp\testexport.xls",0,"Test2"
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    What version of Excel are you running? This may be a very easy exercise with Power Query if your are running 2010 or later. I didn't suggest that since you showed 2003 as your Access Version.

  6. #6
    Kristy is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    7
    Quote Originally Posted by alansidman View Post
    What version of Excel are you running? This may be a very easy exercise with Power Query if your are running 2010 or later. I didn't suggest that since you showed 2003 as your Access Version.

    Access 2000 and I think 2003 Run Time - yeah it's that old. We have newer versions but the way we are set up I have full access and my team is in the run time environment. It works for us but transitioning to the newer versions of access will be difficult because of the code created in this version doesn't always translate well in the newer versions.

  7. #7
    Kristy is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    7
    Any chance you'd want to make a video of this and send it to me so I can copy what you did? or maybe Zoom so you could walk me through it?

  8. #8
    Kristy is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    7
    Thanks guys, I repeated the code, changed the page names and it works.

    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel9, "PCF Automated Agency Checklist Report", _
    "C:\Users\XX - Name\Documents\PCF Report.xls", , "PCF Automated Checklist Report"


    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel9, "PCF Automated License Report", _
    "C:\Users\XX - Name\Documents\PCF Report.xls", , "PCF Automated License Report"


    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel9, "PCF Automated Producer Report", _
    "C:\Users\XX - Name\Documents\PCF Report.xls", , "PCF Automated Producer Report"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2020, 02:39 PM
  2. Replies: 4
    Last Post: 01-12-2017, 01:29 AM
  3. Replies: 6
    Last Post: 01-29-2015, 08:53 PM
  4. Replies: 1
    Last Post: 04-30-2012, 05:10 PM
  5. Export 2 Queries to Same Workbook in Access 2010
    By Mikey in forum Import/Export Data
    Replies: 2
    Last Post: 08-23-2010, 05:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums