Hi, when I use the export interface to export a global table, it is not clearing previous contents from the excel file.
Therefore if I export 3 rows but previously there were 5, I still see two olds from the previous export.
What's the best way to clear the excel file before the export?
thank you
Hey @Lambert, in the Excel Interface > Custom tab, you can write custom code for importing/exporting.
You can write a small script that deletes the sheet using exceldeletesheet, and when you go to Export, it will recreate the sheet. You could also try another excel command; exceldeleterange - deletes a range from the currently open excel workbook.
Hi, thanks for getting back.
I cannot do exceldeletesheet("Sheet1"), I think because there is only one sheet in the file.
And when I try exceldeleterange, I get this error:
could you please assist?
thank you
I was able to reproduce this and make a workaround.
excelsetsheet("Sheet1"); // set the active sheet
// NOTE: This doesn't error, but it doesn't have the desired result of deleting data.
excelsetrange("MyRange",1,1,10,20); // creates a named range
exceldeleterange("MyRange"); // deletes the named range (NOTE: this does not delete the data!)
// HACK: It's annoying to delete the data within the range (you'd need to loop or something), so instead, delete the sheet, using a placeholder sheet to prevent errors.
excelcreatesheet("SheetTemp"); // create a second temporary sheet
exceldeletesheet("Sheet1"); // delete the original sheet
excelcreatesheet("Sheet1"); // re-create the first sheet
exceldeletesheet("SheetTemp"); // delete the temporary sheet
excelclose(1); // save and close the workbook
unfortunately when I run this in a script, FlexSim hangs and I need to close it via crtl+alt+supr
excelopen("export.xlsx");
excelcreatesheet("SheetTemp"); // create a second temporary sheet
exceldeletesheet("Sheet1"); // delete the original sheet
excelcreatesheet("Sheet1"); // re-create the first sheet
exceldeletesheet("SheetTemp"); // delete the temporary sheet
excelclose(1); // save and close the workbook
I cannot make it work :(
It looks like you are running it directly via the script window? I did my testing with the "Custom Export" part of the Excel Import/Export interface. I don't know if the script window is different. I also did not copy/paste the built in part of the example code, but am including it below.
1. Add The example code from the Plus button
2. Edit the example code to include the changes
3. Then you can use the "Execute" button in the Custom tab to run the code.
Please note that the example code only clears the Sheet, it does not do your data export. You still need to run the regular excel export to write your data. You can do the export from the custom script as well, but that involves knowledge of what you are exporting. You could also try adding the excelmultitableexport() command which will run the programmed exports from the Export tab.
And if you enlarge your table you import into your excel sheet to contain more rows? You can resize a FlexSim table easily.
Depending on the complexity of the data and the excel file, that could be tricky to write. I guess you could try to query excel for how many rows are already populated in the sheet, and then expand the table to that many rows before export, but I'm not sure how easy the first part is.
To me this is generally a bug, or at least surprising behavior. If you export data to a sheet in excel, I would expect it to overwrite the whole sheet. I'm thinking of some tables I have that have 7000-10000 rows, depending on how the simulation happened. If I were to write out the results of a run with 7000 rows on an excel sheet that had 10000 already existing from a different run (for debugging or inspection of results), then the results would be erroneous or confusing when trying to debug, which is likely what happened to Lambert here.
I can see some use cases where you don't want it to clear other parts of the sheet, but I would expect those to be in the minority. The user manual should at least tell you that this could happen (writing fewer rows than before will leave the old data there).
13 People are following this question.
FlexSim can help you understand and improve any system or process. Transform your existing data into accurate predictions.
FlexSim is a fully 3D simulation software environment. FlexSim can be used to simulate any process in any industry.
FlexSim®, FlexSim Healthcare™, Problem Solved.®, the FlexSim logo, the FlexSim X-mark, and the FlexSim Healthcare logo with stylized Caduceus mark are trademarks of FlexSim Software Products, Inc. All rights reserved.