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


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, May 26, 2005 5:22 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Excel Macro Automation from within Access

Hi Mark

Here is how (as I learned from Mr. Colby). 
It works very reliably:

Function RunExcelMacros( _
  ByVal strFileName As String, _
  ParamArray avarMacros()) As Boolean

Debug.Print "xl ini", Time
  
  On Error GoTo Err_RunExcelMacros

  Static xlApp      As Excel.Application
  Dim xlWkb         As Excel.Workbook
  
  Dim varMacro      As Variant
  Dim booSuccess    As Boolean
  Dim booTerminate  As Boolean

  If Len(strFileName) = 0 Then
    ' Excel shall be closed.
    booTerminate = True
  End If

  If xlApp Is Nothing Then
    If booTerminate = False Then
      Set xlApp = New Excel.Application
    End If
  ElseIf booTerminate = True Then
    xlApp.Quit
    Set xlApp = Nothing
  End If
  
  If booTerminate = False Then
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strFileName,
UpdateLinks:=0, ReadOnly:=True)
  
    ' Make Excel visible (for troubleshooting only) or not.
    xlApp.Visible = False 'True
    
    For Each varMacro In avarMacros()
      If Not Len(varMacro) = 0 Then
  Debug.Print "xl run", Time, varMacro
        booSuccess = xlApp.Run(varMacro)
      End If
    Next varMacro
  Else
    booSuccess = True
  End If
  
  RunExcelMacros = booSuccess

Exit_RunExcelMacros:

  On Error Resume Next
    
  If booTerminate = False Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
  End If
  
Debug.Print "xl end", Time
  Exit Function

Err_RunExcelMacros:
  Select Case Err
    Case 0      'insert Errors you wish to ignore here
      Resume Next
    Case Else   'All other errors will trap
      Beep
      MsgBox "Error: " & Err & ". " & Err.Description, vbCritical +
vbOKOnly, "Error, macro " & varMacro
      Resume Exit_RunExcelMacros
  End Select

End Function

/gustav

>>> MarkBoyd at McBeeAssociates.com 05/25 11:28 pm >>>
I have a need to run Excel macro code from within Access VBA.
Basically, the code logic flows as follows:
1. Export Access report as a new Excel file (DoCmd.OutputTo
acOutputReport, strXLName, acFormatXLS, , True) 2. Run Excel macro (from
within Access VBA) to format the Excel file accordingly.
 
Does anyone have any sample code and/or resources related to this logic?
Any help is greatly appreciated.
 
Thanks,
Mark Boyd
I/S Supervisor
McBee Associates, Inc.

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