Mass export to excel - data selection based on keys

Daniel Pech

Forums

Hi there,
I am dealing with a mass export of data from idea databases to e.g. excel files now, but unfortunatelly I am not getting on well with that. :-(
I will try to describe the situation:
I prepared a database containing accounting data (booking entries) in IDEA. Then I did a selection based on key values (Data - Selections - Selection based on key values...). The key value is an G/L Account. IDEA created several individual databases (imd files) - for each key value (G/L Account) one database. Now I want to export all the individual databases to excel. There is no option to do that on mass. In case of big amount of key values (e.g. 100), it is annoying to do the export 1 by 1.
I tried to do a test script: I did the export for one value (G/L account 1) first, then I copied the script from "History" and extend the script for the second value (G/L account 2). When I triggerred the script, it went to error ("Error on line xx - Duplicate definition: myArray).
Is there any chance to do the mass export from IDEA?
Thank you for any idea. :-)
Dan

Baricha Saddam Ali Tue, 09/05/2017 - 09:46

Dear Brian
Thank you very much for your response. I appreciate your help. I have a challenge understanding the code you wrote. I am new to IDEA.

Brian Element Tue, 09/05/2017 - 10:05

Hi Baricha,

Well the script I would consider for advanced users as it has you creating code to manipulate Excel.  I will try and explain what the script does.  In the Sub Main you are setting up your list of IDEA files that you want exported and then merged into one Excel file.  You then call the createExcelSheet function.

It first creates an array sExcelName() to hold the names of the Excel file names.  The array is made the same size as the sExcelExportList by using the ubound function (this gets the number of items in an array or the upper boundary of an array).

For For Next loop takes the name of the IDEA filed, strips off the IMD and adds the XLSX file extension.  It then calls the IDEA task to export the file to the Exports.ILB directory under the project folder.

At this point you have all your files as separate Excel files.

The next block of code creates an Excel object and creates a new Excel file in the Exports.ILB folder that will hold all your separate Excel files.

The next block then uses a for next loop that opens each individual Excel spreadsheet, copies the information from the worksheet and then pastes it into a new worksheet in your new Excel spreadsheet.  The code then does an autofit so that the column widths show all the information.

It then closes each of the Excel spreadsheets, once everything has been copied over it saves the new worksheet, closes it and exists Excel.

Hopefully that makes a bit more sense.

Thanks

Brian

Baricha Saddam Ali Tue, 09/05/2017 - 10:47

Dear Brian
I replaced the values in the script and got error 39 (Invalid Database).
Set db = Client.OpenDatabase(sExcelExportList(i)) 
Please see attached.

Brian Element Tue, 09/05/2017 - 10:52

Change the:

ReDim sExcelExportList(5)

to

ReDim sExcelExportList(3)

If you don't it will be looking for 6 databases instead of your 4.

Baricha Saddam Ali Tue, 09/05/2017 - 11:19

Dear Brian
Thank very much, the explanation makes sense.
 

Baricha Saddam Ali Tue, 09/05/2017 - 11:52

Dear Brain
It has partly worked, It exported the 4 files to my IDEA exports but failed creating a new excel workbook for combining the 4 files. 
Kindly note i had done this
Set fso = CreateObject("Scripting.FileSystemObject")
workDir = Client.WorkingDirectory
Path = workDir
 
Kindly see attached.

Brian Element Tue, 09/05/2017 - 12:41

Hi Barachi,

Thanks for also posting the code.  From your code this is mostly like the problem. You placed:

Const EXCEL_FILENAME = "Bulk_Results.XLSX" 'name of new excel file

In the sub main, you need this as a global variable so remove it from the sub main and place it above the sub main, in the area where you declare your Global variables.

That should hopefully fix your error.

Brian

Baricha Saddam Ali Wed, 09/06/2017 - 08:24

Dear Brian 
Apologies for the late response. I want to thank you immensely for your assistance. The code has worked perfectly!. Thank you very much.

Baricha Saddam Ali Wed, 09/06/2017 - 09:07

Dear Brian
One last question. How do I add "check file exist" on exportation script. I tried as attached and it did not work