Solved

PageCopyTableToExcel Procedure - Save Results to Excel

  • 17 March 2022
  • 4 replies
  • 72 views

Badge

Hello, 

 

I want to save the results of various variables into an Excel Workbook. All the results I want to save are on the “Multiple Iteration Dashboard ” Page I created. I read about the function PageCopyTableToExcel  that would allow me to save those results (https://documentation.aimms.com/functionreference/user-interface-related-functions/page-functions/pagecopytabletoexcel.html#pagecopytabletoexcel).

 

You can find my projects attached to this question.

 

To do so, I created a Procedure named “ExportExcel” where you can find the following code : 

 

if not axll::WorkBookIsOpen(WorkbookFilename : "Book1.xlsx" ) then

          axll::OpenWorkBook(WorkbookFilename : "Book1.xlsx" );

endif;

 

PageCopyTableToExcel(pageName: "Multiple Iteration Dashboard",

        tag: "FuelEfficiency",

        includeHeaders: 1,

        selectionOnly: 0,

        ExcelWorkbook: "Book1.xlsx",

        Range: "A1:Z150");

 

I went into the Fuel Efficiency Table Properties to correctly tag it as “FuelEfficiency”.

However, I got this error : “PageCopyTableToExcel(ExcelWorkbook: Book1.xlsx) failed: Unable to get a handle to the workbook.”

The Excel workbook is in the same file as my AIMMS Project so I don’t understand why I got this error. 

 

Could you help me please? Do you now another way to save my results in Excel? 

 

Thank you very much! 

icon

Best answer by gdiepen 19 March 2022, 10:07

View original

4 replies

Userlevel 4
Badge +7

I am not 100% sure, but could this potentially be because this function is depending on the now deprecated Excel functions (that have been replaced with the axll library)?

I think you would need somebody from AIMMS to actually confirm this.

As an alternative, instead of using this convenience function, you can also export the contents of the displayed parameters of the pivot table yourself to an Excel file with the functions in the axll system library (especially the functions like axll::WriteTable to write multidimensional data)

 

 

Badge

Hello, 

I’m trying to do it thanks to the WriteTable function. Therefore, I wrote : 

 

if not axll::WorkBookIsOpen(WorkbookFilename : "Book1.xlsx" ) then

        axll::OpenWorkBook(WorkbookFilename : "Book1.xlsx" );

endif;

 

axll::WriteTable(

        IdentifierReference     : DistanceTraveled_n,

        RowHeaderRange          : "A2:A12",

        ColumnHeaderRange       : "A1:B1",

        DataRange               : "",

        AllowRangeOverflow      : 1,

        WriteZeros              : 1,

        IncludeEmptyRows        : 1,

        IncludeEmptyColumns     : 1,

        IncludeEmptyRowsColumns : 1);

 

If I put some “1” for IncludeEmptyRows, IncludeEmptyColumns and IncludeEmptyRowsColumns arguments, I get this error :  “WriteTable :  is not a simple set, and cannot be used here.”

 

If I put zero instead, I get this error : “WriteTable : Dimension mismatch: 1 <> 1 + 1 (column indices: rows 1:1 + row indices: columns A:A)”. 

Here are the dimensions of the Table I would like to export on Excel… It is a copy-paste from AIMMS when I include the headers. 

 

Userlevel 4
Badge +7

I think you might need to change the  "A1:B1"  into “B1:B1”. You also might need to give the DataRange as “B2:B12”

 

Could be that you need to reduce the 12 to 11 because with these statements, you will not write the row 2 (just containing the n) as this was done by the pivot table copy.

You can take a look at the WriteTable documentation at https://documentation.aimms.com/aimmsxllibrary/api/Multi_Dimensional_Data.html#axll::WriteTable

In your case, you want to write a 1-dimensional parameter.

Badge

Okay it works thank you!!! 

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator