Wednesday, March 4, 2009

Excel COM problem: Call was rejected by callee

I was asked to help with a problem in GAMS/GDXXRW. It threw an exception with the error "Call was rejected by callee". After some experimenting, it turned out that the culprit was a pivot table (and graph). This pivot table was using data being updated by GDXXRW and after removing the pivot table the problem went away. A command line option is added to GDXXRW to give Excel some extra time after the COM Automation Server (i.e. Excel) is started. This will allow the pivot table to initialize without interference from GDXXRW.

7 comments:

  1. Thank you for this post. Seriously, I was going nuts trying figure out what was going on. I had my pivot tables set to automatically refresh because I didn't want to have to refresh like 7 pivot tables. Changing that from auto-refresh made my problem go away, but I got the clue from you, thank you!

    ReplyDelete
  2. Thanks for the feedback on auto-refresh! Erwin

    ReplyDelete
  3. So, I solved my problem a little bit differently than you did after some experimentation. I have a huge GAMS output which is really unwieldy, so I have added about 20 pivot tables to the excel output to synthesize all the data. I wrote a macro in Excel titled auto_open, which uses ActiveWorkbook.RefreshAll to refresh everything in the workbook. I didn't know this previously, but a macro entitled auto_open opens automatically at open. But, it doesn't run when GAMS calls the file, so everything refreshes quite nicely, and you don't have to tell your colleagues to press anything (previously I had added a button users would press to run a similar macro).

    ReplyDelete
  4. Not sure if this helps, but let me describe how I implemented an application that used an Excel front-end and very large pivot tables in reporting the solution. I did not use GDXXRW but rather I used VBA code to import the GDX file. In addition an alternative was offered, where the GAMS model produced large CSV files and the pivot tables used the CSV files as external data sources. This allowed us to handle solution data sets that exceeded the Excel row limit (this limit was 65,536 rows for versions before Excel 2007). These two implementations had none of the problems as described here as they did not use GDXXRW.

    ReplyDelete
  5. Hi Erwin

    I am encountering the exact same issue. Can you give a few more details on the cure you described re the command line params?

    We have auto update on the pivot tables because its entirely possible the user will run in a locked down environment which disables macros. Hence I would love a way which still allows the pivot autoupdate to still be in place.

    Thanks
    Andy C

    ReplyDelete
  6. And thanks for the tip on AutoRefresh Greg, at least that has enabled me to carry on working while I figure out how to make this work.

    ReplyDelete
  7. In 23.1 if you type GDXXRW at the command prompt you'll see the description of the new parameter RWAIT.

    ReplyDelete