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
| < Prev | Next > |
|---|
