Exporting global table to Excel

I want to export data from a global table called 'Output Data' to an excel file called 'Sample.xlsx' in 'Sheet1' after every model run.
This is the code that I tried using on model stop trigger but nothing happens. Help me to correct my error:

excellaunch("C:\Program Files\Microsoft Office\OFFICE11");
excelopen("D:\Seven Oaks Project\Final models\Sample.xlsx");
excelsetsheet("Sheet1");
excelexporttable("Output Data",1,1,20,18);

Hi,

are you sure, you have installed the compatibility pack for office 2007, 2010 and 2013? Office11 is Office2003. That version hasn't got the ability to handle xlsx files by standard.
And you checked the language version to be US or English, because otherwise the characters may defer for row and column.
And the file example.xlsx does already exists with the sheet?
And you have read http://www.flexsim.com/community/for...26&postcount=8.
And you think, the MTEE Multiple Table Excel Export does not work for you, too?
And the next time tell us something more, you have already tried, because then it is easier to determine what you can check.


Jörg

you might also try with double slashes:
excellaunch("C:\\Program Files\\Microsoft Office\\OFFICE11");

1.) I have office 2010. I copied this path from the default settings in the excel toolbox.
2.) language of Flexsim or excel ?? Dint get your point here.
3.) Yes the file named Sample.xlsx exists with sheet 1 inside it.
4.) Yes I understand that excalelaunch, excelquit and excelopen, excelclose commands need to be called in pairs.
5.) I tried the MTTE but the code dint work there either. Moreover as I said I want the global table to be exported automatically at the end of each model run.

Which operation system do you run?
Which bit version of the operation system do you run, 32 Bit or 64 Bit?
Which bit version of excel do you run?
All these answers could lead to a different path to the excel program file.
Try one of these paths for windows7:
C:\Program Files\Microsoft Office\Office14
C:\Program Files (x86)\Microsoft Office\Office14

Jörg

I use Windows 8 64 bit, and excel is 32 bit

Originally Posted by Jörg Vogel

Which operation system do you run?
Which bit version of the operation system do you run, 32 Bit or 64 Bit?
Which bit version of excel do you run?
All these answers could lead to a different path to the excel program file.
Try one of these paths for windows7:
C:\Program Files\Microsoft Office\Office14
C:\Program Files (x86)\Microsoft Office\Office14

Jörg

I found out the path to where excel.exe is located and I wrote the following code on on model stop:

excellaunch("C:\Program Files (x86)\Microsoft Office\Office14");
excelopen("C:\Users\Arjun\Dropbox\Seven Oaks Project\Sample.xlsx");
excelsetsheet("Sheet1");
excelexporttable("Output Data",1,1,20,18);
excelclose(1);
excelquit();

Still nothing appears in the above mentioned sheet

The backslash character is the escape character for special characters in string literals in C++ and FlexScript.

Your code

excellaunch("C:\Program Files (x86)\Microsoft Office\Office14");

should be

excellaunch("C:\\Program Files (x86)\\Microsoft Office\\Office14");

Originally Posted by Phil BoBo

The backslash character is the escape character for special characters in string literals in C++ and FlexScript.

Your code

excellaunch("C:\Program Files (x86)\Microsoft Office\Office14");

should be

excellaunch("C:\\Program Files (x86)\\Microsoft Office\\Office14");

Thanks it did work. But I have a new problem that I don't think their is a solution to. I actually wanted to export a table of about 50000 rows and 31 columns for each run in the experimenter. But it does not work on such a large file and throws up all sorts of errors. I guess I will have to go old style i.e. save states from experimenter, later load them one by one and copy paste the whole table. It is a cumbersome and boring job that is a huge bottleneck between doing the experiments and analyzing for presenting to the client.

You could just use the exporttable() command to save the table as a csv file. Then open the csv file in Excel.

I just tested this with a 50000 x 31 global table and it works fine (it took less than 3 seconds to execute):

exporttable(reftable("GlobalTable1"), concat(modeldir(), "test.csv"));

You don't need to use the Flexscript Excel communication commands to do what you want to do.

What would I need to do if I have to export the table to a particular sheet in the excel file?
And this code does not work with the experimenter :-(

The point of that code is that it isn't interacting with Excel directly. That's why it is so fast and efficient. It is writing the data to a text-file in a comma-separated value format.

You can then use Excel to read these files into an Excel Workbook:

http://office.microsoft.com/en-us/ex...010099725.aspx
http://smallbusiness.chron.com/open-...eet-40281.html
http://www.ablebits.com/office-addin...port-csv-excel

If you have proficiency with Excel, you could automate this process to suit your specific needs with macros.
https://www.google.com/search?q=exce...sv+into+sheets

I forgot that modeldir() doesn't currently work with the new experimenter. You can use this code to get the model directory in an experiment child process:

concat(gets(node("MAIN:/project/environment/compilesave/modeldir")), "test.csv")

Thanks a lot. I understood your point. Guess I will finally have to learn excel macros. Been thinking about it for a long time.

Hi Arjun,

The following links will help you in studying the macros.

http://www.wikihow.com/Write-a-Simpl...icrosoft-Excel
http://office.microsoft.com/en-in/ex...010014111.aspx


Regards,

Arun KR
CCS,India

Originally Posted by Phil BoBo

The point of that code is that it isn't interacting with Excel directly. That's why it is so fast and efficient. It is writing the data to a text-file in a comma-separated value format.

You can then use Excel to read these files into an Excel Workbook:

http://office.microsoft.com/en-us/ex...010099725.aspx
http://smallbusiness.chron.com/open-...eet-40281.html
http://www.ablebits.com/office-addin...port-csv-excel

If you have proficiency with Excel, you could automate this process to suit your specific needs with macros.
https://www.google.com/search?q=exce...sv+into+sheets

I forgot that modeldir() doesn't currently work with the new experimenter. You can use this code to get the model directory in an experiment child process:

concat(gets(node("MAIN:/project/environment/compilesave/modeldir")), "test.csv")

Actually this code does not work. On debugging I found that gets(node("MAIN:/project/environment/compilesave/modeldir") returns nothing. There seems to be no string value of the node! What am I missing?!

Originally Posted by mearjun

Actually this code does not work. On debugging I found that gets(node("MAIN:/project/environment/compilesave/modeldir") returns nothing. There seems to be no string value of the node! What am I missing?!

Normally, you can get the model directory with the modeldir() command, but as you found out, that command doesn't currently work on experimenter child processes. Instead you can read it using that code. If you use that code in a script window or with the debugger, it will be an empty string. You have to use it on an experimenter child process.

See the attached model. It does what you want to do in the experimenter's On Replication End trigger.

Using the exporttable command is far faster than writing to Excel. However, if using Excel is what you want to do, here are a few things that will help.

The reason you get errors is because FlexSim creates multiple isntances of itself when you run the Experimenter. Each instance tries to access Excel simultaneously, which throws errors. Though it will take drastically longer to run a large experiment, you can solve this issue by forcing FlexSim to only create one instance of FlexSim at a time while running the Experimenter. To do this, create a node in the VIEW:/environment called expmaxcores, adding number data and setting it's value to 1.

As for your excel code, you might find it more useful to utilize the excelmultitableexport() command. This acts exactly the same as pressing the Export Tables button in the Excel Interface. You can set up your export parameters in the Excel window and then call the command (ensuring the lines are checked that you wish to export).

Hope this helps.