[AccessD] Automate Excel macro with parameters

John,

    Local functions in excel, accepting variable number of arguments as an array, can be run from access by using a variant to hold the array. 

    Sample function Fn_RunFunctionInExcelWbook() in access module, as given below, demonstrates the approach. 

    The local function in excel formats and highlights the cells as per their respective data type. Separate color is used for column heading row. Outer and inner borders also get inserted. 

    Typical statement in access module for calling the above access function, which in turn makes use of local functions and subroutines in excel, is given below. It posts data from access table to sheet 1 of excel workbook, starting at cell B2:

    Call Fn_RunFunctionInExcelWbook("<<FullPathOfExcelFile>>", _
                                            "<<NameOfAccessTable>>", 1, 2, 2)

    Note - In view of size limitation for this post, local excel functions used by access function below, would be furnished in the next post

Best wishes,
A.D. Tejpal
------------

' Sample function in access for running local function in
' excel, supplying variable number of arguments via array
'=====================================
Function Fn_RunFunctionInExcelWbook( _
                        ExcelFilePath As String, _
                        TableName As String, _
                        SheetNumber As Long, _
                        StartRow As Long, _
                        StartColumn As Long) As Long
    ' Reference reqd: DAO 3.6 and Excel Object Library
    ' Runs local function in excel with array arguments
    ' held in a variant (Size of array can be variable).
    ' Apart from the array input, the excel function
    ' accepts optional arguments for sheet number,
    ' start row and start column.
    
    ' Excel cells get formatted and colored as per data
    ' type. Separate color is used for column headings.
    ' Outer and inner borders also get inserted
    
    ' The excel local function returns number of cells
    ' populated.on each run.
    
    ' This access function returns overall total number
    ' of cells populated (including column headings) as
    ' per information returned by excel function.
    
    On Error GoTo ErrTrap
    Dim exp As Excel.Application
    Dim wb As Excel.Workbook
    Dim rst As DAO.Recordset
    
    Dim Rtv As Variant, Cnt As Long
    Dim Tot As Long, Rw As Long
        
    ' Get an instance of currently open excel application
    On Error Resume Next
    Set exp = GetObject(, "Excel.Application")
    ' Create new instance if excel is not currently open
    If Err.Number <> 0 Then
        Set exp = New Excel.Application
    End If
    On Error GoTo ErrTrap

    exp.UserControl = False   ' Helps closing of excel
    Set wb = exp.Workbooks.Open(ExcelFilePath)
    
    ' Clear existing borders and highlights in excel sheet
    ' (using excel local subroutine).
    ' Note - This statement can be disabled if so desired
    exp.Run "P_ClearBordersAndColors", SheetNumber
    
    Set rst = CurrentDb.OpenRecordset(TableName)
    ' Build array for populating excel cells
    ReDim Rtv(rst.Fields.Count - 1)
    Tot = 0
    Rw = 0
    Do Until rst.EOF
        For Cnt = 0 To rst.Fields.Count - 1
            If Rw > 0 Then
                ' Build an array of field values to be used as
                ' data in excel.
                Rtv(Cnt) = rst.Fields(Cnt).Value
            Else
                ' Build an array of field names to be used as
                ' column headings in excel.
                Rtv(Cnt) = rst.Fields(Cnt).Name
            End If
        Next
        
        ' Populate excel cells by running local function
        ' in excel, using array arguments (represented by
        ' variant Rtv) along with other optional arguments.
        Tot = Tot + exp.Run("Fn_ExcelLocal", Rtv, _
                        SheetNumber, Rw + StartRow, _
                        StartColumn)
        
        If Rw > 0 Then
            rst.MoveNext
        End If
        
        Rw = Rw + 1
    Loop
    
    ' Run local function in excel for providing outer
    ' borders for data block as well as special color
    ' for column headings row.
    exp.Run "P_DressUpDataBlock", _
                        SheetNumber, StartRow, _
                        StartColumn
    
    MsgBox "Done. Excel cells populated = " & Tot
    
ExitPoint:
    On Error Resume Next
    
    Fn_RunFunctionInExcelWbook = Tot
    
    wb.Save
    wb.Close True
    exp.UserControl = False   ' Helps closing of excel
    exp.Quit

    Set wb = Nothing
    Set exp = Nothing

    Set rst = Nothing
    On Error GoTo 0
    Exit Function

ErrTrap:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitPoint
End Function
'==================================

  ----- Original Message ----- 
  From: jwcolby 
  To: Access Developers discussion and problem solving 
  Sent: Friday, February 27, 2009 20:20
  Subject: [AccessD] Automate Excel macro with parameters


  I am trying to figure out how to automate an excel macro with an unknown number of parameters.  I doubt it is possible but I thought I would ask.

  <<Snipped>>

  The only way I can see to do this (and it is UGLY) is to build a case statement that looks at how many variables are in the paramarray and just select a different xlbook.Application.Run statement:

  (pseudocode)

  select case ubound(MyParamArray)
  case 1
  xlBook.Application.Run "MacroName", MyParamArray(0)
  case 2
  xlBook.Application.Run "MacroName", MyParamArray(0), MyParamArray(1)
  case 3
  xlBook.Application.Run "MacroName", MyParamArray(0), MyParamArray(1), MyParamArray(3)
  end select

  Do that for a number that would meet most cases and punt.

  I HATE punting!

  Has anyone ever solved the problem of passing the contents of a ParamArray to another object that accepts a paramarray?
  -- 
  John W. Colby
  www.ColbyConsulting.com