[AccessD] Excel Macro Automation from within Access

Thanks Jim.  This is perfect!

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: Thursday, June 02, 2005 10:10 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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.
 
-----------------------------------------
This message and any attachments are intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by replying to this message, and then delete it from your system.
-------------------------------------------