I have a requirement to generate a macro enabled .xlsm file from Oracle and was wondering the best way to achieve this.
The file is multi tabbed and includes various buttons and macros however all that I need to generate is the table data from the database.
I would imaging that the best way would be to store a blank version of the file with all the macros etc in place and then open it up and add in the table data using PL/SQL. Am I on the right lines here?
I have had a quick look and there seem to be a number of ways to do this including creating a jsp, using the COM integration or just writing the whole thing in PL/SQL. I am leaning towards the PL/SQL generation using UTL_FILE as I'm more proficient in that area but wanted to see if anyone had any comments/experience in this area. If opening up the file and inserting table data is the way forward then it might not be so complicated but I'm sure there will be lots of issues as there usually is with something like this!
Thanks.
The only idea that I have on my mind is to create Excel document with all macros, buttons and logic in Excel as separate document. Then export data from Oracle database to another plain xlsx document and connect those two documents.
So your macros enabled document will use external data from document generated/exported from database.
You have various methods to export data to Excel document from Oracle database, like simple CSV file or by using some tools like ora_excel (http://www.oraexcel.com) which is able to do more and format document.
I've a lot experience with PL/SQL and actually don't know for any toll which is able to generate macro enabled Excel sheets with PL/SQL.