asked on

Extract queries to Excel tabs

Hello,

I am trying to extract two queries into a workbook using a template but only the last query inserts into its tab. The first tab is blank, can you please help?

Do While Not rstTables.EOF
DoCmd.OpenQuery "QdelTblEmailPayTemp", acViewNormal, acEdit
strSql = "INSERT INTO TblEmailPayTemp([Contact],[EmailAddress],[FirstName])" & _
"SELECT TblEmailPayments.Contact, TblEmailPayments.EmailAddress, TblEmailPayments.FirstName FROM TblEmailPayments WHERE TblEmailPayments.Contact = " & Chr$(34) & rstTables.Contact & Chr$(34) & ";"

FileName = "S:\Finance\Accounting Operations\National Accounts\Databases\EmailedParplans\" & [rstTables].[Contact] & "MPP.xls"
FileNameR = "S:\Finance\Accounting Operations\National Accounts\Databases\EmailedParplans\" & [rstTables].[Contact] & "MPP.pdf"
DoCmd.RunSQL strSql
FileCopy "C:\MonthlyParplanDetail.xls", "C:\WINDOWS\MonthlyParplanDetail.xls"
DoCmd.OutputTo acOutputQuery, "ParPlanDetails", acFormatXLS, "C:\WINDOWS\MonthlyParplanDetail.xls.ParPlanDetails", False, ""
DoCmd.OutputTo acOutputQuery, "ParPlanSummary", acFormatXLS, "C:\WINDOWS\MonthlyParplanDetail.xls.ParPlanSummary", False, ""

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CFMI

ASKER

Yes, I am trying to put data from different queries on separate sheets in the same workbook. I will try the Transfer spreadsheet statement...

CFMI

ASKER

Hello,
Can you please help with my Transfer syntax?
DoCmd.TransferSpreadsheet Export, acSpreadsheetTypeExcel7, acOutputQuery, "ParPlanDetails", acFormatXLS, "C:\WINDOWS\MonthlyParplanDetail.xls.ParPlanDetails", No

Rey Obrero (Capricorn1)

what version of office are you using ?

CFMI

ASKER

The TransferSpreadsheet command worked as you indicate the worksheet name within the syntax. Thanks!

Rey Obrero (Capricorn1)

for Office 2003

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ParPlanDetails", "C:\WINDOWS\MonthlyParplanDetail.xls", True,"ParPlanDetails"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ParPlanSummary", "C:\WINDOWS\MonthlyParplanDetail.xls", True,"ParPlanSummary"

Helen Feddema

Here is some sample code:

strPath = Application.CurrentProject.Path
strWorkbook= strPath & "Customers.xls"

DoCmd.TransferSpreadsheet transfertype:=acExport, _
   spreadsheettype:=acSpreadsheetTypeExcel8, _
   tablename:="qryCustomers", _
   FileName:=strWorkbook, _
   hasfieldnames:=True

DoCmd.TransferSpreadsheet transfertype:=acImport, _
   spreadsheettype:=acSpreadsheetTypeExcel9, _
   tablename:=strTable, _
   FileName:=strWorkbook, _ 
   hasfieldnames:=True, _
   Range:=strRange

Open in new window