Exporting Global Tables with Excel 2007

I used to be able to export my global tables using the Custom Export commands like:

excelopen(filename); // open a link with an Excel workbook
excelsetsheet("MyTab"); // set the active worksheet - sheet names should
excelexporttable("MyTable",1,1,numRows,1 );

But I've recently upgraded my Microsoft Office suite and now have Excel 2007.....and now I can't export. Is there a patch or upgrade for my FlexSim that will allow it to export using the new Excel?
Thanks,
David White

David,

I hope you don't mind I removed your email address from your post. I did this so that it wouldn't be picked up if someone's program searches our website for email addresses to add to a spam list. If you want to put it back up you can and I won't take it down again (Use the edit button to edit your post), but I figured better safe than sorry.

As for you question I don't know why this would happen but I'm sure someone on the forum will.

Good luck with your question,
Brandon

David,

I'm using Excel 2007 and the excelexporttable() command is working for me. Are you calling excelclose() when you are done? If so, are you passing any parameters to it? Remember that excelclose(0) will close the workbook without saving any changes and excelclose(1) will save your changes before writing to it.

A.J.

Thanks Brandon and AJ. I didn't change anything but now it's working???? I did reboot during that time, so maybe my computer was just fried and I didn't know it. Thanks for your responses though.

http://support.microsoft.com/default...;en-us;Q319832

When trying to run the custom ExcelExport code mentioned above multiple times, I receive the following error:

"The object invoked has disconnected from its clients."

I have not been able to spend a large amount of time on this problem, but I noticed the article above states that is a known issue.

I did not find any posts of other FlexSim users encountering this problem so I was curious if there is a solution or perhaps I'm not using the ExcelExport correctly the second+ times?

Thanks!
Jonathan Laird

Jonathan,

This isn't an error that I've ever seen. It may be something in your code. Flexsim's Excel communication system can be pretty picky about how it expects you to make certain function calls. If you're missing something or doing something in the wrong order strange things can happen. I wonder if this is one of those times. Can you share a simplified version of your code or model with us so we can see what's happening? Thanks.

A.J.

A.J.,

It might be the code. I'll post the code below with steps to reproduce the error. Thanks for looking into the issue.

Warning: This will most likely produce OLE exception pop-ups in FlexSim and prevent FlexSim from closing (requiring Task Manager to end it).

Assumption: 'filename' contains a path to an excel file
Description: 'numRows' will get the count of how many data entries there are in another table (#2). Method 'excelexporttable' will then export 'numRows' quantity from table (#2).
________________________
excelopen(filename); // open a link with an Excel workbook
int numRows;
numRows = gettablenum("TatCounts",1,1) ;
excelcreatesheet("TatStatToAdapterUnload");
excelsetsheet("TatStatToAdapterUnload");
excelexporttable("TatStatToAdapterUnload",1,1,numR ows,1 );
________________________

1. Create an empty workbook excel file named 'TestExport.xls'.
2. Place the code above into the 'Custom Export' Excel GUI button code, changing the names of the Global Table references if need be.
3. Make a Global Table with numeric data only in column 1 (20-50 values at least). Note: Remove 'numRows' and simply use hard-coded value such as 20-50.
4. Save your work in FlexSim.
5. Click the 'Custom Export' button to export data to Excel.
6. Select the open 'TestExport.xls' file and all data should be exported correctly.
7. Clicking the 'Custom Export' button again should be okay. However, if you were to close the Excel file 'TestExport.xls' the issue occurs...
8. After closing the Excel file, click 'Custom Export' again.
9. The OLE pop-up errors should appear. Must end-task FlexSim and restart to make export excel work again.

Question - Is it mandatory to close the excel workbook at the end of the code block? //excelclose()

Thanks,
Jonathan

Jonathan,

Yes. You have to call excelclose(). There are two sets of commands that work in pairs when you are communicating between Flexsim and Excel. If you call excelopen() then you MUST call excelclose(). If you call excellaunch() you must call excelquit(). The open/close pair is always used when communicating with Excel. The launch/quit pair isn't used much anymore. It used to be mandatory, but now it just slows things down so I don't recommend using it.

Roughly, what happens in the Excel communication system is that Flexsim establishes a connection to the workbook and Excel when excelopen() is called. If you close that connection by closing the workbook yourself, it will confuse Flexsim and later calls to the Excel commands may not work. If that connection is still active when you try to close Flexsim, Flexsim will wait until it is gone to close. That's why you've had to use the Task Manager to close Flexsim.

Note that you can use excelsave() or excelclose(1) to save the changes to the file before it is closed.

A.J.

A.J.,

I figured it had to be something like that as I was completing my post... and thought I'd ask the basic question.

Going back and rereading the command documentation it does say to call excelclose(). Well, sorry to waste your time. Thought I read the command description, but I probably only breezed over it.

Jonathan Laird