[AccessD] Excel Macro Automation from within Access

I think the sequence you are looking for is similar to this:

Sub rpt_to_excel()
Dim appExcel As Excel.Application, strpathname As String
Dim strpathnew As String, strReport As String

strpathname = "C:\test.xls"
strpathnew = "C:\test2.xls"
strReport = "rptInvsummary"
DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, strpathname

 Set appExcel = New Excel.Application
 
 'format your report
    With appExcel
        .Workbooks.Open strpathname, 0
        .Visible = True 'just to watch the sheet
        .Range("A1:G1").Select
        .Selection.Font.Bold = True
        .Selection.Font.Name = "Arial"
        .Selection.Font.Size = 12
        .ActiveWorkbook.SaveAs Filename:=strpathnew, FileFormat:=xlNormal
    End With
     appExcel.Quit
    Set appExcel = Nothing
End Sub

This opens the file you output and saves it as a current version workbook
under a new name. 

As you debug be aware of leaving orphan instances of Excel in memory. You
can tell if any exist by placing the following script in notepad and running
it:

Dim objXL
	Dim strMessage
	
	On Error Resume Next
	
	'	Try to grab a running instance of
	'	Excel...
	Set objXL = GetObject(, "Excel.Application")
	
	'	What did we find?..
	If Not TypeName(objXL) = "Empty" Then
		strMessage = "Excel Running."
	Else
		strMessage = "Excel Not Running."
	End If
	
	'	Feedback to user...
	MsgBox strMessage, vbInformation, "Excel Status"
	
	'	Make the Excel instance visible 
	'	if we found one
	if strMessage = "Excel Running." then _
				objXL.Visible = true 

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
Sent: Wednesday, June 01, 2005 5:21 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Excel Macro Automation from within Access


Thanks Jim, this is pointing me in the right direction.

The export process is as follows:
1. Export report to Excel
2. Format report w/ macro
3. Save formatting changes
4. Attach file to email message

When using the SaveAs method, I'm prompted to overwrite the existing
excel file (the one just formatted).  Am I missing something in regard
to the properties of the SaveAs method, that will allow me to save the
existing, formatted file?  If I select Yes to overwrite the existing
file, all of the macro formats are lost.


Mark Boyd
I/S Supervisor
McBee Associates, Inc.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Wednesday, June 01, 2005 3:40 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Excel Macro Automation from within Access

Are you saving the file using code? Check out Excel's SaveAs method. It
allows a fileformat parameter. I think xlWorkbookNormal is the correct
one.

Jim Hale


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.