[AccessD] Automate Excel macro with parameters

John,

    In my previous post, function Fn_RunFunctionInExcelWbook() in access module,  passing variable number of arguments to a function in excel workbook, was provided.

    The function as well as certain subroutines in excel workbook, that are run via the access function mentioned above, are given below. 

    (On account of prescribed limitation in message size, these could not be accommodated in the earlier post)

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

' Local functions / subroutines in excel workbook
' (General Module)
'===================================
Function Fn_ExcelLocal(Vr As Variant, _
            Optional SheetNum As Long = 1, _
            Optional StartRow As Long = 2, _
            Optional StartColumn As Long = 2) As Long
    ' Populates excel cells as per the array represented
    ' by variant argument Vr
    ' Cells get formatted as per data type.
    ' Each column is highlighted in distinct color
    ' (Separate colors for different data types i.e.
    ' Text / Number / Date). Overall data block is
    ' provided with inner & outer borders.
    ' Special color is assigned to the row representing
    ' column headings.
    Dim Cnt As Long
    
    With Sheets(SheetNum)
        For Cnt = 0 To UBound(Vr)
            With .Cells(StartRow, StartColumn + Cnt)
                .Value = Vr(Cnt)
                If IsDate(Vr(Cnt)) Then
                    .NumberFormat = "dd/mmm/yyyy"
                    .Interior.ColorIndex = 20
                Else
                    If IsNumeric(Vr(Cnt)) Then
                        .NumberFormat = "0"
                        .Interior.ColorIndex = 40
                    Else
                        .NumberFormat = "@"
                        .Interior.ColorIndex = 19
                    End If
                End If
                ' Apply border to the cells that have been populated.
                .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
            End With
        Next
    End With
    
    Fn_ExcelLocal = Cnt     ' Number of cells populated
    
End Function
'--------------------------------------------------------

Sub P_DressUpDataBlock(SheetNum As Long, _
                                    StartRow As Long, _
                                    StartColumn As Long)
    Dim rg As Excel.Range
    
    With Sheets(SheetNum)
        ' Apply thick outer border for the whole block
        Set rg = .Cells(StartRow, StartColumn).CurrentRegion
        rg.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        
        ' Apply spl color for column headings row
        Range(.Cells(rg.Row, rg.Column), .Cells(rg.Row, rg.Column + _
                    rg.Columns.Count - 1)).Interior.ColorIndex = 15
    End With
    
    Set rg = Nothing
End Sub
'--------------------------------------------------------

Sub P_ClearBordersAndColors(SheetNum As Long)
    With Sheets(SheetNum)
        ' Clear Any existing borders or highlights
        With .Cells
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    End With
End Sub
'=====================================

  ----- Original Message ----- 
  From: A.D.Tejpal 
  To: Access Developers discussion and problem solving 
  Sent: Monday, March 02, 2009 17:51
  Subject: Re: [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
  ------------