[AccessD] Excel Macro Automation from within Access

I believe MicoSith is vaguely ashamed of the Kill statement since it derives
from a simpler era when such barbaric practices were the norm.  Rumors
indicate, as part of their agreement with the European Union, the command
will be 
renamed TerminatedWithoutPrejudice. The damage may have already been done,
however, since certain large files have already requested amnesty and are
seeking refuge in certain unnamed hidden folders. :-)

Jim Hale

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


Great idea.  I wasn't aware of the Kill statement.  My plan was to shell
a batch file to delete it, but this is much easier.

Thanks again,
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: Monday, June 06, 2005 2:43 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Excel Macro Automation from within Access

I'm glad it worked for you. I was thinking that you might want to add a 
	Kill strpathname 'deletes original Excel sheet statement after
the new copy has been saved so you are left with only the new copy.

Jim Hale

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
Sent: Monday, June 06, 2005 11:31 AM
To: Access Developers discussion and problem solving
Subject: RE: [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.



***********************************************************************
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.
-------------------------------------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
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.