Mass export to excel - data selection based on keys

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

Change the:

ReDim sExcelExportList(5)

to

ReDim sExcelExportList(3)

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

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

My pleasure, glad you have it working.

Brian