[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

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


Thanks to everyone who helped with this issue.  I greatly appreciate it.
The only issue that I'm now having is the excel file seems to be created
with Excel 5.0/95.  I'm using Office 2003, and the excel file is created
with ("DoCmd.OutputTo acOutputReport, strXLName, acFormatXLS, "c:\" &
strXLName & ".xls", False").  Why does Access create the file using a
lower version of Office than what is running on the client?  Is there a
way to specify the Office version when exporting the file?

FYI - The macro automation function is below:

Function ExcelMacro(ByVal strFileName As String) On Error GoTo
Err_ExcelMacro
    Dim xlApp         As Excel.Application
    Dim xlWkb         As Excel.Workbook
    
    If fIsAppRunning("Excel") Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If
       
    xlApp.Application.Visible = True
    Set xlWkb = xlApp.Workbooks.Open(strFileName, 0)
    
    'run Excel macro code here
    '...
    '...
    'end macro code

Exit_ExcelMacro:
On Error Resume Next
    
    Set xlWkb = Nothing
    Set xlApp = Nothing
    Exit Function

Err_ExcelMacro:
    Select Case Err
        Case 0      'insert Errors you wish to ignore here
            Resume Next
        Case Else   'All other errors will trap
            MsgBox Err.Number & " - " & Err.Description, vbCritical,
"Error"
            Resume Exit_ExcelMacro
    End Select
End Function

Thanks again,
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.
-------------------------------------------