5 Replies Latest reply on May 23, 2013 12:38 AM by 1010229

    Oracle APEX and Excel Macros

    AnilKapuria
      I have an Oracle APEX application that contains data tables with the data and generates reports, and I have a set of Excel macros that must work on the data contained in two of these data tables, and then store the output data generated from Excel macros in a new data table, so that APEX can generate use Excel macro processed data in reports.
      Is it somehow possible to embed the macros in APEX app, and execute it on the input data from the data tables, and store processed data back in a new data table?

      Thank you,
      Anil
        • 1. Re: Oracle APEX and Excel Macros
          Vite DBA
          Hi Anil,

          it's not possible to embed the Excel macros into Apex. I think the best course of action would be to port the Excel logic into a PL/SQL procedure and have it execute directly on the data.

          Regards
          Andre
          • 2. Re: Oracle APEX and Excel Macros
            618972
            There are situations where the ability to apply a macro or not, make a big difference: when you need heavy formatting, pivot generation and so on.

            In this situation, I've put the macro in a separate excel file that the user can download after the report data. (as a simple file download)
            Another way, that I've never tried, may be generating excel output with product like Birt, BI ..
            Thanks
            Stefano

            Image example

            (You can create autostartup macro that scan all open sheets to "recognize" the report to process... But this are Excel activities, not Apex...)

            Edited by: scorradi on May 13, 2013 3:20 PM

            Edited by: scorradi on May 13, 2013 3:20 PM
            • 3. Re: Oracle APEX and Excel Macros
              1010229
              Thanks for your response.

              Oracle APEX Application that relies on Excel Workbook Macro to perform certain functions. This combined application must then run in Oracle cloud, so that users can access it.

              I am new to Oracle, and would greatly appreciate help with:

              (a) with a pl/sql procedure that selects columns from the two data tables,
              (b) put them in one .xlsx file in a directory,
              (c) open .xlsm separate workbook that auto execute the macro and
              (d) store the processed data back in new data tables in APEX application for interactive reports and dashboard display?

              Thank you,
              Anil
              • 4. Re: Oracle APEX and Excel Macros
                Vite DBA
                Hi Anil,

                you will find it difficult to find anyone here willing to help you with your requirements.

                The reason for that is simply that anything you can do in Excel in terms of data processing and manipulation, you can achieve with greater ease and performance within the Oracle database using SQL, PL/SQL and if really required, Java. And this is before you even start to consider the implications of automatically unloading the data to a spreadsheet and then reloading it back to the database in a processed format.

                As someone who is new to Oracle, this is something you should know now. The Oracle RDBMS is not just a datastore, it is a complete and powerful data processing system. Go forth and learn how to use it and you will soon learn how to sneer at and deride people who talk of the power of spreadsheets.

                Regards
                Andre
                • 5. Re: Oracle APEX and Excel Macros
                  1010229
                  Hi Andre,

                  The only reason I am stuck with Excel Macro is that my application relies heavily on Excel's Goal Seek and Solver functions, and I am unable to find their equivalent in Oracle APEX, nothing would be better than having complete application in Oracle APEX.

                  I wonder if you know any equivalent functions to Excel's Goal Seek and Solver functions.

                  Thanks,
                  Anil