Tim Richardson

Melbourne, Australia

  • Increase font size
  • Default font size
  • Decrease font size

Building Accpac reports in excel: part 2

E-mail Print PDF

In this article, I'm going to build an Excel report to query AR; that is, a pivot table aging report.

The more advanced techniques demonstrated here are

a) connecting to accpac from within an Excel macro

b) using an Accpac view to safely update the Accpac database

Pivot tables are a great reporting tool. In this case, we have our customers grouped into sales channels, so the pivot table starts by summarising per sales channel, and allows drill down to customer. Another click lets the user drill down into invoice detail. We also show credit limit, terms, last payment date. We have the same format for AP with the same drill-down capabilities.

I will assume the reader knows basic Excel macros.

Accpac does all the hard work of aging: there is a temporary table which very neatly ages all open items. However, we can't simply query this table in Excel using the SQL approach I explained in part 1, because the temporary table needs to be updated first; this aging table is not updated whenever something changes in AR. What we do is get Accpac to do the work, and this can be done from within Excel, via a macro which connects to Accpac.Accpac bundles program logic into "views"; updated AR is a certain view, and the job of our macro is to run that view.

Working out which view you want is pretty easy. I recorded a macro while running the existing AR Aging report, and in the code generated, I learnt how to invoke the view. Recording macros in Accpac is not the same as running macros in Excel; hopefully this tutorial will bridge the gap.

This was developed on Accpac 5.5A.

 

Setting up Excel

Excel needs to access Accpac programs for these macros to work, so you should develop the macro on a computer with the Accpac client software installed (that is, a machine that you can use Accpac on). The Accpac runtime programs are usually stored on a server; users who run this macro need read-access to that location (all Accpac users will automatically have this).

Before this macro will work, you need to add a project reference to the accpac library. In the VBA editor. Go to the Tools menu and edit references, and tick the check box for ACCPAC COM API Object 1.0. You may need to scroll to look for it. If you can't see it, Accpac is not correctly configured on your computer. Later versions may exist; try the higher version number you see. You will see the Location of this library when you select it (for me, the location is \\server\departments\accounts\AAS\Accpac\RUNTIME)

You'll need a user with permission to execute AR Aging. In our environment we have a user with low privileges called ARVIEW.

 

Remember the Accpac uses the term "view" to mean accessible prices of Accpac functionality. The view we are using here is called AR0055.

The Accpac HTML documentation documents views as well as tables; it shows you which parameters each view has.

Below, the sub-routine which connects to AccPac and executes the view

 Various parameters are set with a UserForm, which I don't show here.

 

'define COMPANYNAME as a CONST set to the name of the database

Private Sub updateAccPac()
    On Error GoTo ACCPACErrorHandler
    Dim Session As AccpacCOMAPI.AccpacSession
    Set Session = CreateObject("Accpac.Session")
 
    'An accpac user called ARVIEW is defined with low privileges
    ' simpleword is the password

    Session.Init "", "AS", "AS1000", "55A"
    Session.Open "ARVIEW", "SIMPLEWORD", COMPANYNAME, Date, 0, "" 'use APVIEW as the user
        
    'inserted code from the macro recorder
    Dim temp As Boolean
    Dim ARAGING1 As AccpacCOMAPI.AccpacView
    Dim ARAGING1Fields As AccpacCOMAPI.AccpacViewFields
   
    Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
   
    Set mDBLinkCmpRW = Session.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
   
    mDBLinkCmpRW.OpenView "AR0055", ARAGING1 'this is the view we want to use
    Set ARAGING1Fields = ARAGING1.Fields
    Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
    Set mDBLinkSysRW = Session.OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
   
   
    temp = ARAGING1.Exists
    ARAGING1.RecordClear
    Dim endOfLastMonth As Date
    endOfLastMonth = Application.WorksheetFunction.EoMonth(Now(), -1) '
    Load UserForm1
    'set userform default values
    'UserForm1.tb_AgeAsOF = endOfLastMonth
    UserForm1.DP_AgeAsOF = endOfLastMonth
    UserForm1.TB_CutOffPeriod = (((Month(endOfLastMonth) + 6 - 1) Mod 12) + 1)
    UserForm1.TB_CutOffYear = Year(endOfLastMonth) + IIf(UserForm1.TB_CutOffPeriod <= 6, 1, 0)
   
   
    UserForm1.Show
    If Not GoodToGo Then
        Set Session = Nothing
        End
    End If
    gAgeAsOf = UserForm1.DP_AgeAsOF
   
    ARAGING1Fields("CMNDCODE").PutWithoutVerification ("52")              ' Command Code
    'ARAGING1Fields("RUNDATE").PutWithoutVerification (endOfLastMonth) ' Age as-of Date
    ARAGING1Fields("RUNDATE").PutWithoutVerification (gAgeAsOf) ' Age as-of Date
  
    ARAGING1Fields("AGEINVDTSW").PutWithoutVerification ("0")             ' Age by
    ARAGING1Fields("AGEPERIOD2").PutWithoutVerification ("30")            ' First Period
    ARAGING1Fields("AGEPERIOD3").PutWithoutVerification ("60")            ' Second Period
    ARAGING1Fields("AGEPERIOD4").PutWithoutVerification ("90")            ' Third Period
    ARAGING1Fields("SWOPTMETER").PutWithoutVerification ("1")             ' Display Meter
    ARAGING1Fields("AGEINVDTSW").PutWithoutVerification ("0")    '0 means age by   due date
    Dim cutoffToday As Boolean
    Dim cutoffDate As Date
    Dim choice
  
   ' cutoffToday = False 'if false it means cutoff by period
    If UserForm1.OB_SpecificPeriod Then
        cutoffToday = False 'if false it means cutoff by period
    Else
        cutoffToday = True
        cutoffDate = Now() 'default but may be overwritten
        If UserForm1.OB_SpecificDate Then
            cutoffDate = UserForm1.DTPicker1
        End If
       
    End If
   
   
    If cutoffToday Then
        ARAGING1Fields("SWCUTOFFBY").PutWithoutVerification ("0")             ' Cutoff By Doc Date
       
        ARAGING1Fields("CUTOFFDATE").PutWithoutVerification (cutoffDate)
        'defaults to today?
    Else
         ARAGING1Fields("SWCUTOFFBY").PutWithoutVerification ("2") 'Year/Period
         'ARAGING1Fields("CUTOFFPERD").PutWithoutVerification (((Month(endOfLastMonth) + 6 - 1) Mod 12) + 1)
         ARAGING1Fields("CUTOFFPERD").PutWithoutVerification (UserForm1.TB_CutOffPeriod)
         ARAGING1Fields("CUTOFFYEAR").PutWithoutVerification (UserForm1.TB_CutOffYear)
    End If
    ' Period
    'If Not cutoffToday Then MsgBox ("Cutoff date is end of period " & (((Month(endOfLastMonth) + 6 - 1) Mod 12) + 1))
    If Not cutoffToday Then MsgBox ("Cutoff date is end of period " & UserForm1.TB_CutOffPeriod & " in year " & UserForm1.TB_CutOffYear)
   
    ARAGING1.Process  'this executes Accpacs aging logic, updating the temp table
   
    gSeqNum = ARAGING1Fields("AGESEQ").Value  'the sequence number tells us the set of aging records just updated
       
    Set Session = Nothing
   
Exit Sub
ACCPACErrorHandler:
  Dim lCount As Long
  Dim lIndex As Long
  If Errors Is Nothing Then
       MsgBox Err.Description & ". Possibly too many users logged in."
  Else
      lCount = Errors.Count
      If lCount = 0 Then
          MsgBox Err.Description & ". Possibly too many users logged in."
      Else
          For lIndex = 0 To lCount - 1
              MsgBox Errors.Item(lIndex)
          Next
          Errors.Clear
      End If
      Resume Next
  End If
   
End Sub

Next step

Next step: query the data and return it to a worksheet, ready for use by pivot tables.

This function is below: this is the function which the user runs.

 

Sub UpdateAgingPivTable()
'
   Call updateAccPac 'this works for AR but commented out to help test the rest of the macro
   
    'now we should be able to get the ARAGED table data

 

    Dim destsheet As Worksheet
    Set destsheet = Worksheets("ARDATA")
    destsheet.Cells.Clear
   
    Dim oRs As ADODB.Recordset
    Dim sConn As String
    Dim sSQL As String
    Dim o1Rs As ADODB.Recordset
    Dim s1Conn As String
   
   sConn = "Provider='SQLOLEDB';Data Source='database';Integrated Security='SSPI';Initial Catalog='" & COMPANYNAME & "'"
    sSQL = "SELECT araged.IDCUST, araged.IDINVC, araged.DATEINVC, araged.DATEDUE, araged.AMTINVCTC, araged.AMTINVCHC, " & _
    "araged.AMTDUE1TC, araged.AMTDUE1HC, araged.AMTDUE2TC, araged.AMTDUE2HC, araged.AMTDUE3TC, araged.AMTDUE3HC, araged.AMTDUE4TC, " & _
    " araged.AMTDUE4HC, araged.AMTDUE5TC, araged.AMTDUE5HC, araged.AMTDUE6TC, araged.AMTDUE6HC, araged.AMTDUE7TC, araged.AMTDUE7HC, " & _
    "araged.AMTDUE8TC, araged.AMTDUE8HC, araged.AMTDUE9TC, araged.AMTDUE9HC, araged.TOTBKWDTC, araged.TOTBKWDHC, araged.TOTFWDTC, " & _
       "araged.TOTFWDHC , araged.AMTBALDUET, araged.AMTBALDUEH, arcus.codecurn, arcus.NAMECUST, arcus.CODETERM, arcus.AMTCRLIMT, " & _
       "arcus.DATELASTPA, arcus.amtlastpyt, arcus.idgrp " & _
    "FROM dbo.araged araged, dbo.ARcus ARcus where araged.IDCUST = ARcus.idcust " & _
    "AND ARAGED.AGESEQ = " & gSeqNum
   
    ' Create and Open the Recordset object.
    Set oRs = New ADODB.Recordset
    oRs.CursorLocation = adUseClient
    oRs.Open sSQL, sConn, adOpenStatic, _
                 adLockBatchOptimistic, adCmdText
    ' oRs.Open sSQL, sConn
                   
    'MsgBox oRs.RecordCount
    Dim totalDueHC_col As Integer
    Dim totalDueTC_col As Integer
   
    Dim icols
    For icols = 0 To oRs.Fields.Count - 1
      ' but wait
      Select Case oRs.Fields(icols).Name
        Case "AMTDUE2TC", "AMTDUE2HC"
            destsheet.Cells(1, icols + 1).Value = "DUE30_" & oRs.Fields(icols).Name
        Case "AMTDUE3TC", "AMTDUE3HC"
           destsheet.Cells(1, icols + 1).Value = "DUE60_" & oRs.Fields(icols).Name
        Case "AMTDUE4TC", "AMTDUE4HC"
           destsheet.Cells(1, icols + 1).Value = "DUE90_" & oRs.Fields(icols).Name
        Case "AMTBALDUET"
            totalDueTC_col = icols + 1
            destsheet.Cells(1, icols + 1).Value = oRs.Fields(icols).Name
        Case "AMTBALDUEH"
            totalDueHC_col = icols + 1
            destsheet.Cells(1, icols + 1).Value = oRs.Fields(icols).Name
        Case Else
            destsheet.Cells(1, icols + 1).Value = oRs.Fields(icols).Name
      End Select
    Next
   
    destsheet.Range("a2").CopyFromRecordset oRs
    Dim rowIter As Integer
    Dim custID As String
    custID = ""
    For rowIter = 2 To destsheet.Range("a1").CurrentRegion.Rows.Count
        If custID = destsheet.Cells(rowIter, 1) Then
            'it's not the first row of this customer
            destsheet.Cells(rowIter, totalDueTC_col).Value = 0
            destsheet.Cells(rowIter, totalDueHC_col).Value = 0
        Else
            custID = destsheet.Cells(rowIter, 1)
        End If
       
    Next rowIter
    ' workbook.SaveAs
    ' oRs.MarshalOptions = adMarshalModifiedOnly
    ' Disconnect the Recordset.
    oRs.Close
    Set oRs.ActiveConnection = Nothing
   
    Dim myPivotCache As PivotCache
    'Set myPivotCache = Worksheets("Arpivot").PivotTables("ArPivot").PivotCache
    'myPivotCache.Refresh

 

    Set myPivotCache = Worksheets("Due and Payments").PivotTables("DueAndPayments").PivotCache
    myPivotCache.Refresh
    Worksheets("due and payments").Range("A2").Value = "Aged:"
    Worksheets("due and payments").Range("B2").Value = gAgeAsOf

 

End Sub
Tags:

Add this to your website
Last Updated on Thursday, 16 December 2010 08:00