[AccessD] Automating Excel

[McGillivray, Don]

Hi John,

I probably cribbed the gist of this from somewhere else, but here's something I found in one of my apps.  This is code from an Access app and was designed to manipulate an existing Excel file, but there may be some things you can adapt to your purpose.  I never actually implemented this, but I believe I got it working as it is here.  As I recall, "qryTemp" is a named range that I expected to find in the existing Excel file, and of course the field names were specific to that table.

Watch for word wrap . . .

Function PivotExcelData(strPathFile As String, Optional blnMakeVisible As Boolean = False)
    On Error GoTo ErrorHandle

    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wsh As Excel.Worksheet
    Dim pvTbl As Excel.PivotTable
    Dim pvFld As Excel.PivotField
    
    Set xl = New Excel.Application
    xl.Visible = blnMakeVisible
    Set wbk = xl.Workbooks.Open(strPathFile)
    
    Set wsh = wbk.Sheets.Add
    wsh.Name = "PivotTable"
    wsh.Select
    
    Set pvTbl = wsh.PivotTableWizard(xlDatabase, "qryTemp")
    
    'Set up the rows
    Set pvFld = pvTbl.PivotFields("DateRemHead")
    pvFld.Orientation = xlRowField
    Set pvFld = pvTbl.PivotFields("Mode")
    pvFld.Orientation = xlRowField
    Set pvFld = pvTbl.PivotFields("DateRODHead")
    pvFld.Orientation = xlRowField
    
    'Set up the columns
    Set pvFld = pvTbl.PivotFields("Fund")
    pvFld.Orientation = xlColumnField
    Set pvFld = pvTbl.PivotFields("RevenueCode")
    pvFld.Orientation = xlColumnField
    
    'Set up the data
    Set pvFld = pvTbl.PivotFields("DistTotal")
    pvFld.Orientation = xlDataField
    pvFld.Function = xlSum
    pvFld.NumberFormat = "#,##0.00"

    'Set up the page filter
    Set pvFld = pvTbl.PivotFields("RemitNum")
    pvFld.Orientation = xlPageField

FunctionExit:
    On Error Resume Next
    Set pvFld = Nothing
    Set wsh = Nothing
    wbk.Save
    wbk.Close
    Set wbk = Nothing
    xl.Quit
    Set xl = Nothing
    Exit Function

ErrorHandle:
    Select Case Err.Number 
        Case Else
            MsgBox Err.Number & " " & Err.Description & vbCrLf & vbCrLf _
                & "Error in procedure Function 'PivotExcelData' of VBA Document 'Form_frmViewRemit'"
            Resume FunctionExit
    End Select

End Function

Hope this is useful.

Don

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, July 26, 2018 11:21 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>; jwcolby at gmail.com
Subject: [AccessD] Automating Excel

I have always automated Excel by doing a "recorder capture", saving that as vba in a template spreadsheet and then called that function.

I need to do a whole set of cross tabs.  Has anyone actually performed crosstabs in this manner?  ATM I paste the data into a spreadsheet, then manually select crosstab from the menu, then manually drag the three sets of data, the column names, row names and data, into their respective positions in the crosstab widget that pops up from the menu item.

-- 
John W. Colby

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com