I have can save my report as an Excel workbook, but not to a specific
sheet in that workbook.

The code that I have developed so far is as follows:

Public Sub ExcelSales()

Dim objXL As New Excel.Application

    objXL.Visible = True
    objXL.Workbooks.Open "H:\Application Development\Amgen\P & L
Template.xls"
    objXL.Windows(1).Visible = True
    objXL.Worksheets(1).Range("A1:G25").Clear
    objXL.Worksheets(2).Range("A1:G25").Clear
    objXL.Worksheets(3).Range("A1:G25").Clear
    objXL.Worksheets(4).Range("A1:G25").Clear
    objXL.Worksheets(5).Range("A1:G25").Clear

    objXL.Worksheets(1).Activate
    DoCmd.SelectObject acReport, "srptSales", True
    ??? DoCmd.RunCommand acCmdSaveAs ???
         ***THIS IS WHERE I AM AT A LOSS***

    objXL.Workbooks(1).Close SaveChanges:=True

    objXL.Quit
    Set objXL = Nothing

End Sub

If it is not possible to "push" the report from Access to Excel, does
anyone have code to "pull" the report into Excel from Access?

--
TIA,
Randy Shore
Data Design
805.494.3439

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

Access has no ability to export to a specific worksheet using its standard
functions like transfer spreadsheet.  To accomplish what you are trying to
do, what you need to do is to attach (link) each spreadsheet page from your
workbook to the Access db, and then run an append query based on your
subreport's underlying queries to append the data to a target spreadsheet.
To attach a specific spreadsheet in the workbook, you'd use code like:

Dim Cdb as Database
Dim tmpTableDef as TableDef
Set Cdb = CurrentDb
Set tmpTableDef = Cdb.CreateTableDef("MyTempXLSheet1")
tmpTableDef.Connect = "Excel 5.0;HDR=NO;DATABASE=" & strTargetXLFilePath
tmpTableDef.SourceTableName = "XLWorksheet1Name"

then run an append query to the worksheet and delete the table def.

HTH
--
Steve Arbaugh
ATTAC Consulting Group
http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm

What I ended up doing was to export my 5 different sub-report results
to 5 new workbooks, and then using automation from Access, selected the
data in each temp workbook and pasted it onto the appropriate worksheet
of the actual workbook.  It uses alot of resources as I end up with 6
instances of excel open at one time (actually, 6 different workbooks,)
but at least it works consistently.

--
Regards,
Randy Shore
Data Design
805.494.3439

Sent via Deja.com http://www.deja.com/
Before you buy.