Generation of Excel reports from IDEA

alex_terieur

Forums

Hello everyone,

I am new here, and I am discovering the power of IDEA for managing large amounts of data (sorry for the quality of my English!).

Unfortunately, I have not yet found a way to easily comment on certain data lines, except by exporting to Excel and then entering a comment in a new column.

I recently discovered at a presentation session organized by CaseWare that there is, for example, a "FEC" module for France. This application allows you to perform predefined tests on a company's accounting data and then export them to Word or Excel format. What I liked about these reports is the ability to obtain formatted Excel files with the option to add comments or a conclusion.

Naturally, I am not looking to perform the same tests on the data, but what I really liked was the automatic generation of Excel reports.

Unfortunately, the scripts are not accessible (which I understand), but I imagine that it is possible to create your own scripts in VBA (or another language?) to generate Excel reports automatically and in a synthetic form.

I searched the forum but didn't really find any elements that could help me. I apologize in advance if I missed a similar post!

My goal is to automatically generate 30 Excel files for 30 selected accounting account numbers from a connection to an Oracle database containing a general ledger.

The idea is that for each selected accounting account, I can query:
- A first database (data source 1) to get a first synthetic table by grouping the general ledger lines according to certain fields.
- A second database (data source 2) to get a second synthetic table, which details only one type of expense, by grouping the general ledger lines according to certain fields.

And then combine these two synthetic tables into an Excel report, automatically formatting them a bit and adding free comment columns, for example as shown below:

Currently, I am doing multiple extractions in IDEA (for each accounting account I choose, I perform 2 extractions), which I then copy to Excel using pivot tables to get the totals, and then copy into a synthetic Excel report to comment on.

If you have any leads to help me save time, that would be great! Also, feel free to let me know if anything is unclear.

Thank you very much in advance.

Best regards,

Alex

Images

Brian Element Tue, 04/29/2025 - 07:42

Hi Alex and welcome to the site.

Your first point, if you want to add comments to line items in IDEA that can be done.  What you need to do is create an editable character field through the field manipulation and give it a length of 1024 (the max).  You can then add comments directly to the IDEA database.  Let me know if you need more details on how to create a new field.

Yes, you can create Excel spreadsheets through IDEAScript.  Give me a bit of time and I will put together some code for you on how to do it or at least how to start.

Brian

alex_terieur Tue, 04/29/2025 - 11:47

Here is the translation in English:

---

Thank you very much, Brian, for your response!
Indeed, I would appreciate it if you could look into my issue with creating Excel sheets.
In the meantime, I tried your first solution (which doesn't quite match what I want to do), but I got an error message saying "a valid equation is required for this field."
Thank you in advance.

alex

Brian Element Tue, 04/29/2025 - 13:54

I found this code that hopefully will get you started:

Option Explicit
Dim sFile As String

Sub Main
	sFile = "D:\My IDEA Documents\IDEA Projects\Samples\Exports.ILB\Sample-Detailed Sales.XLSX"
	Call ExportDatabaseXLSX()	'Sample-Detailed Sales.imd
	Call modifyExcelSheet()
End Sub

Function modifyExcelSheet()
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	
	Set excel = CreateObject("Excel.Application")
	excel.Visible = False
	Set oBook = excel.Workbooks.Open(sFile)
	Set oSheet = oBook.Worksheets.Item(1)
	oSheet.Cells.Select
	oSheet.Cells.EntireColumn.AutoFit
	oSheet.Cells(1, 1).Font.Name = "Arial"
	oSheet.Cells(1, 1).Font.Size = 12
	oSheet.Cells(1, 1).Interior.Color = 65535
	
	Set oSheet = Nothing
	oBook.Save
	oBook.Close (True)
	Set oBook = Nothing
	excel.quit
	Set excel = Nothing
End Function


' File - Export Database: XLSX
Function ExportDatabaseXLSX
	Dim db As database
	Dim task As task
	Dim eqn As String
	
	Set db = Client.OpenDatabase("Sample-Detailed Sales.imd")
	Set task = db.ExportDatabase
	task.IncludeAllFields
	eqn = ""
	task.PerformTask sFile, "Database", "XLSX", 1, db.Count, eqn
	Set db = Nothing
	Set task = Nothing
End Function

The script will first export an IDEA database as an xlsx file.  It then opens the file in IDEAScript and does some modification.  Generally when I am trying to figure out the code I do what I want to do in Excel but I turn on the Record Macro in the Developer ribbon.  Once I am finished I will go in and look at the code that was recorded and then adapt it to IDEAScript.  Unfortunately you can't do a copy paste as the code usually needs a bit of modification.  In IDEAScript you can only reference one cell or a group of cells at a time and you need to reference them each time you make a change.

Let me know if you need more to get you started.