[AccessD] Access and Excel Integration - Resources?

Hi Brad

Here is how (as I learned from Mr. Colby) to run a series of Excel macros from Access:
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


>>> BradM at blackforestltd.com 11-07-2011 20:39 >>>
I am doing some R&D work involving Access/Excel integration (Windows
Automation).

I just finished reading a book titled "Excel and Access Integration".
It is a well-written book, but it does not get into a great deal of
depth with regards to the "Automation" realm.

I was wondering if there is a resource  (book or website) that covers
this area in more depth.

In a nutshell, I would like to be able to do anything that a person can
do in "native Excel" via commands in Access 2007 which control Excel.

Thanks for your help.
Brad