Using VBA to Create a GL Account List in Excel

This article has been archived. It is offered "as is" and will no longer be updated.
TechKnowledge Content
Question: Can VBA and Report Writer be used to create a list of accounts in Excel?

Answer: Yes, it can. Here is a sample of how create a VBA script in Dynamics that will transfer a list of accounts to an Excel spreadsheet. This example uses VBA in Report Writer to establish a link between Dynamics and Excel. The VBA link is triggered when you print an Account List in Dynamics. VBA will transfer the Account Number, Account Description, and the Typical Balance to Microsoft Excel and then sort the list by the Account Description. To run this code, you will need to have Microsoft Excel 97 installed on your machine. You will also need make sure that you add the Dynamics Account List Report and the Account Number, Account Description and Typical Balance Report Fields to your VBA project. Finally, to run this code in your Dynamics project, you will need to add a Reference to "Microsoft Excel 8.0 Object Library". This can be done from Tools-References. 'Created 4/2/98, GPS - DynamicTools Support, djn 'Sample Code created in Dynamics R4 VBA 'This sample transfers an Account List to an Excel Spreadsheet. 'VBA code is triggered from an Account List printed in Dynamics.

Option Explicit
Dim RowID As Integer
Dim GLAccountSheet As Object

Private Sub Report_Start()
'This procedure creates the Excel spreadsheet object 'and sets up conditions for the integration 'Create the Excel object Set GLAccountSheet = CreateObject("excel.sheet")
'Initialize the RowID variable. This is used later to 'increment the row where account number data is added RowID = 2 'Change the name of the sheet
GLAccountSheet.ActiveSheet.Name = "GL Account List"
'Set Column Headings GLAccountSheet.ActiveSheet.Range("A1").Value = "Account Number" GLAccountSheet.ActiveSheet.Range("B1").Value = "Account Description" GLAccountSheet.ActiveSheet.Range("C1").Value = "Typical Balance"
End Sub

Private Sub Report_BeforeBody(SuppressBand As Boolean)
'This procedure builds an Excel table containing all GL Account ' Numbers, Account Descriptions, and Typical Balance GLAccountSheet.ActiveSheet.Cells(RowID, 1) = AccountNumber
GLAccountSheet.ActiveSheet.Cells(RowID, 2) = AccountDescription
GLAccountSheet.ActiveSheet.Cells(RowID, 3) = BalanceType
RowID = RowID + 1
End Sub

Private Sub Report_End()
'This procedure builds displays the Excel sheet 'Set the width of the table's column
GLAccountSheet.ActiveSheet.Range("A1").ColumnWidth = 20
GLAccountSheet.ActiveSheet.Range("B1").ColumnWidth = 40
GLAccountSheet.ActiveSheet.Range("c1").ColumnWidth = 13
'Make column headings bold
GLAccountSheet.ActiveSheet.Range("A1:C1").Font.Bold = True
'Use VBA's Sort method to sort the table by the Account Description
GLAccountSheet.ActiveSheet.Range("A2:C" + CStr(RowID)).Sort _ Key1:=GLAccountSheet.ActiveSheet.Range("B2")
'Display the spreadsheet in Excel GLAccountSheet.Application.Visible = True
End Sub

This article was TechKnowledge Document ID: 9557

Article ID: 867129 - Last Review: 12/29/2014 15:46:08 - Revision: 6.0

  • Microsoft Dynamics GP 9.0
  • Microsoft Business Solutions–Great Plains 8.0
  • Microsoft Business Solutions–Great Plains 7.5
  • Microsoft Great Plains Dynamics 7.0
  • Microsoft Great Plains eEnterprise 7.0
  • Great Plains Dynamics 6.0
  • Great Plains eEnterprise 6.0
  • Modifier with Visual Basic for Applications
  • kbnosurvey kbarchive kbmbsmigrate KB867129