Automatically export the contents of a Global table at the end of a run.

Question

question

Allen Greenwood asked May 10, '21 Ben Wilson commented May 19, '21

Automatically export the contents of a Global table at the end of a run.

I need to export data in a Global Table when a model ends. The data needs to overwrite the current contents of the Sheet. I tried using excelexporttable in the OnRunStop Model Trigger but data are appended not overwritten. I also tried deleting Sheet1 then adding Sheet 1 but that didn't work.

Software Version:

FlexSim 21.1.2

flexsim 21.1.2 global table excel export

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Ben Wilson ♦♦ commented · May 19, 2021 at 02:22 AM

Hi @Allen Greenwood, was Benjamin W2's answer helpful? If so, please click the red "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.

0 ·

Answer 1 · 2021-05-10T21:22:19Z

Benjamin W2 answered May 10, '21 Allen Greenwood commented May 12, '21

Hi @Allen Greenwood,

I think the issue you are running into is trying to delete the only sheet in the workbook. Each workbook must contain at least one sheet. In the attached example, I created my own sheet in excel called "MySheet":

Then, I have the following code:

excelopen("ExportTest.xlsx");
exceldeletesheet("MySheet");
excelcreatesheet("MySheet");
excelexporttable("GlobalTable1",1,1,4,1);
excelclose(1);

This will give the appearance of overwriting the data instead of just appending them.

ExportTest.xlsx ExcelExportTest.fsm

1620681518218.png (2.3 KiB)

exporttest.xlsx (8.7 KiB)

excelexporttest.fsm (27.4 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Allen Greenwood ♦ commented · May 11, 2021 at 08:57 PM

Thanks Ben. It makes sense that Excel needs one workbook - I didn't think about that. However, when I run your model, I get a message saying "Could not open file ExportTest.xlsx."

I had been using a similar approach to yours, except the extra sheet, but the table values are not written to the Excel file and the sheet is not deleted. Below is the code I have in the OnRunStop ModelTrigger. I even inserted the excelsetsheet command but no change. My Excel file has two sheets Sheet1 and Output. The code runs okay and I get the msg that the code executed, but no changes to the Excel file.

Also, why does this code not fire each time I Stop a run? I am stopping twice, once at the end of warmup and once at the end of the simulation.

I look forward to your next response. Thanks.

/**Custom Code - Write output table at end of run*/

string FileName = "Output_1.xlsx";

string Sheet = "Output";

Table Output = reftable("Output_1");

int NumRows = Output.numRows;

int NumCols = Output.numCols;

string OutTable = "Output_1";

excelopen(FileName);

excelsetsheet(Sheet);

exceldeletesheet(Sheet);

excelcreatesheet(Sheet);

excelsetsheet(Sheet);

excelexporttable (OutTable,1,1,NumRows, NumCols);

msg("Excel Export","Export of output file is complete",1);

excelclose(1);

0 ·

question