Results 1 to 5 of 5
  1. #1
    JumpyJim is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    2

    Two queries, export to Excel.

    Hi, new to Access and first post so be gentle.



    I have two queries that generate two lists of names. There is no relationship, they are simply to be treated as a table of separate text. I want to export Query 1 to Column A in Excel and Query 2 into Column B of the same workbook. However, if I could transfer the results of both queries to a temporary table via a third query that could capture both, that would be ideal as I could then dump to Excel or to a table.

    The query code for the first:

    Code:
    SELECT [emno] & " " & [fname] & " " & [sname] AS PG
    FROM tblPCImport
    ORDER BY tblPCImport.sname;
    The second query:

    Code:
    SELECT Right([ID],6) & " " & [Fname(s)] & " " & [Sname] AS CMS
    FROM tblCurrent
    ORDER BY tblCurrent.Sname;
    Any thoughts?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    TransferSpreadsheet won't allow exporting to specific spreadsheet/column. According to MS on TransferSpreadsheet: "Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook."

    I think will require VBA manipulating recordset object to populate temp table or VBA manipulating Excel objects.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JumpyJim is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    2
    Hi June7, thanks for that. I do have a VBA method that works but it is clunky, both queries can be written to a workbook into separate columns on the same sheet as long as the workbook has named ranges (in the case, A:A and B:B are the ranges).

    Code:
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qryPCPG", FileName:="G:\Directory\Data_Export.xls", Range:="PGRange"
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qryPCMS", FileName:="G:\Directory\Data_Export.xls", Range:="CMSRange"
    When I use acSpreadsheetTypeExcel12Xml (.xlsx) it doesn't work but with acSpreadsheetTypeExcel9 (.xls) it does.

    It would just be a lot easier if I could take those two queries and make them append to a blank table and then dump the table. I have a table with just two fields and no PK, whichever query is ran first to append the table and field works fine, say it adds 700 records but when I append the second query it will append to the correct field but start at row 701 of the other field, I can't get it to start at row one.

    Append query 1
    Code:
    INSERT INTO tblPGvsCMS ( PG )SELECT [emno] & " " & [fname] & " " & [sname] AS PG
    FROM tblPCImport
    ORDER BY tblPCImport.sname;
    Append query 2
    Code:
    INSERT INTO tblPGvsCMS ( CMS )SELECT Right([ID],6) & " " & [Fname] & " " & [Sname] AS CMS
    FROM tblCurrent
    ORDER BY tblCurrent.Sname;

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "Append" means to ADD new records to a table. I think you are wanting an Update query.

    Excel type 12 with xlsx does not work, it is a known issue.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The page I found that info on says referencing a range (named or otherwise) for export will fail. But it works for you?

    An UPDATE SQL action will not work because the two datasets do not have relationship.

    So, if your Excel export works, it is probably the least clunky method available to you.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. export access queries to excel sheet
    By akapag22 in forum Import/Export Data
    Replies: 1
    Last Post: 06-30-2015, 12:49 AM
  2. Replies: 6
    Last Post: 01-29-2015, 08:53 PM
  3. Export Queries to Excel with Combo-Box visible in Excel
    By johnmarc2 in forum Import/Export Data
    Replies: 1
    Last Post: 07-07-2014, 05:33 PM
  4. Export Queries to Excel tabs with Save as Dialog box
    By deepanadhi in forum Import/Export Data
    Replies: 1
    Last Post: 05-16-2012, 10:36 AM
  5. Replies: 1
    Last Post: 05-28-2009, 05:08 AM

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