Exporting to the same XLS File (1 Viewer)

F

FLabrecque

Registered User.

Local time

Today, 05:41

Joined

Nov 9, 2004

Messages

93

• Nov 16, 2004

• #1

Hi everyone! Thanks in advance for any answers you can give me.

I would like to export the data of 2 tables in the same XLS files, but on different Sheets. I have figured out how to do it manually, but I need to code it.

So far I've tried exporting to an existing file, but it overwrites it.

Current syntax used:
DoCmd.OutputTo acOutputTable, "tblBill", acFormatXLS, "c:\Bill.xls", False

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 05:41

Joined

Aug 30, 2003

Messages

36,125

• Nov 16, 2004

• #2

I do this; the second one goes to a new sheet rather than overwriting the first:

Code:Copy to clipboard

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDiagnosticExport", "C:\W" & Me.CarNum & ".xls", True
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDiagDriveConcern", "C:\W" & Me.CarNum & ".xls", True

 

F

FLabrecque

Registered User.

Local time

Today, 05:41

Joined

Nov 9, 2004

Messages

93

• Nov 17, 2004

• #3

Thanks, it works. Is acSpreadsheetTypeExcel9 for MS-Excel 2000? It's kind of very blurry on that.

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 05:41

Joined

Aug 30, 2003

Messages

36,125

• Nov 17, 2004

• #4

That's my understanding, but I don't think I've seen a reference that verifies it.

 

A

anissw

Registered User.

Local time

Today, 08:41

Joined

Jun 11, 2012

Messages

55

• Jan 4, 2013

• #5

Hello-
I am interested in having this applied to my database, however, I wanted to know how to updated the command for a group by? The output for each sheet will be grouped by customerID. One Excel files with approximately 25 sheets which is based on customer name.

Thank you-

Anissw