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