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;