This article has been archived. It is offered "as is" and will no longer be updated.
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