ATTENTION

This FlexSim Community Forum is read-only. Please post any new questions, ideas, or discussions to our new community (we call it Answers) at https://answers.flexsim.com/. Our new Question & Answer site brings a modern, mobile-friendly interface and more focus on getting answers quickly. There are a few differences between how our new Q&A community works vs. a classic, threaded-conversation-style forum like the one below, so be sure to read our Answers Best Practices.


flexsim.com

Go Back   FlexSim Community Forum > FlexSim Software > Q&A
Downloads

Q&A Using Flexsim and building models

  #1  
Old 02-15-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default 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);
  #2  
Old 02-16-2014
Jörg Vogel's Avatar
Jörg Vogel Jörg Vogel is offline
Flexsim User
 
Join Date: Sep 2007
Location: Hannover, Germany
Posts: 643
Downloads: 35
Uploads: 0
Thanks: 802
Thanked 665 Times in 410 Posts
Rep Power: 642
Jörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond repute
Default

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
The Following 2 Users Say Thank You to Jörg Vogel For This Useful Post:
Tom David (02-17-2014)
  #3  
Old 02-16-2014
Steven Hamoen's Avatar
Steven Hamoen Steven Hamoen is offline
Talumis, Flexsim Distributor, The Netherlands
 
Join Date: Aug 2007
Location: Soest, NL
Posts: 854
Downloads: 43
Uploads: 0
Thanks: 391
Thanked 661 Times in 379 Posts
Rep Power: 684
Steven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond reputeSteven Hamoen has a reputation beyond repute
Default

you might also try with double slashes:
excellaunch("C:\\Program Files\\Microsoft Office\\OFFICE11");
The Following User Says Thank You to Steven Hamoen For This Useful Post:
Tom David (02-17-2014)
  #4  
Old 02-17-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

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.
The Following User Says Thank You to mearjun For This Useful Post:
Jörg Vogel (02-18-2014)
  #5  
Old 02-18-2014
Jörg Vogel's Avatar
Jörg Vogel Jörg Vogel is offline
Flexsim User
 
Join Date: Sep 2007
Location: Hannover, Germany
Posts: 643
Downloads: 35
Uploads: 0
Thanks: 802
Thanked 665 Times in 410 Posts
Rep Power: 642
Jörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond repute
Default

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
  #6  
Old 07-31-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

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

Quote:
Originally Posted by Jörg Vogel View Post
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
  #7  
Old 07-31-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

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
  #8  
Old 07-31-2014
Phil BoBo's Avatar
Phil BoBo Phil BoBo is offline
Flexsim Development
 
Join Date: Jan 2008
Posts: 756
Downloads: 109
Uploads: 18
Thanks: 385
Thanked 1,483 Times in 525 Posts
Rep Power: 1174
Phil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond repute
Default

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

Your code
Code:
excellaunch("C:\Program Files (x86)\Microsoft Office\Office14");
should be
Code:
excellaunch("C:\\Program Files (x86)\\Microsoft Office\\Office14");
The Following 2 Users Say Thank You to Phil BoBo For This Useful Post:
mearjun (08-04-2014)
  #9  
Old 08-04-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

Quote:
Originally Posted by Phil BoBo View Post
The backslash character is the escape character for special characters in string literals in C++ and FlexScript.

Your code
Code:
excellaunch("C:\Program Files (x86)\Microsoft Office\Office14");
should be
Code:
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.
  #10  
Old 08-04-2014
Phil BoBo's Avatar
Phil BoBo Phil BoBo is offline
Flexsim Development
 
Join Date: Jan 2008
Posts: 756
Downloads: 109
Uploads: 18
Thanks: 385
Thanked 1,483 Times in 525 Posts
Rep Power: 1174
Phil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond repute
Default

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):
Code:
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.
The Following 3 Users Say Thank You to Phil BoBo For This Useful Post:
RalfGruber (08-08-2014)
  #11  
Old 08-04-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

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 :-(

Last edited by mearjun; 08-04-2014 at 04:48 PM. Reason: Update
  #12  
Old 08-04-2014
Phil BoBo's Avatar
Phil BoBo Phil BoBo is offline
Flexsim Development
 
Join Date: Jan 2008
Posts: 756
Downloads: 109
Uploads: 18
Thanks: 385
Thanked 1,483 Times in 525 Posts
Rep Power: 1174
Phil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond repute
Default

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:

Code:
concat(gets(node("MAIN:/project/environment/compilesave/modeldir")), "test.csv")
The Following 3 Users Say Thank You to Phil BoBo For This Useful Post:
mearjun (08-04-2014)
  #13  
Old 08-04-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

Thanks a lot. I understood your point. Guess I will finally have to learn excel macros. Been thinking about it for a long time.
  #14  
Old 08-05-2014
arunkrmahadeva's Avatar
arunkrmahadeva arunkrmahadeva is offline
CCS, India
 
Join Date: Sep 2013
Location: India
Posts: 71
Downloads: 67
Uploads: 0
Thanks: 285
Thanked 101 Times in 51 Posts
Rep Power: 145
arunkrmahadeva is a jewel in the rougharunkrmahadeva is a jewel in the rougharunkrmahadeva is a jewel in the rougharunkrmahadeva is a jewel in the rough
Default

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

Last edited by arunkrmahadeva; 08-05-2014 at 12:45 AM. Reason: add links
  #15  
Old 08-05-2014
mearjun mearjun is offline
Flexsim User
 
Join Date: Feb 2012
Posts: 123
Downloads: 5
Uploads: 0
Thanks: 27
Thanked 19 Times in 11 Posts
Rep Power: 119
mearjun will become famous soon enough
Default

Quote:
Originally Posted by Phil BoBo View Post
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:

Code:
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?!
  #16  
Old 08-05-2014
Phil BoBo's Avatar
Phil BoBo Phil BoBo is offline
Flexsim Development
 
Join Date: Jan 2008
Posts: 756
Downloads: 109
Uploads: 18
Thanks: 385
Thanked 1,483 Times in 525 Posts
Rep Power: 1174
Phil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond reputePhil BoBo has a reputation beyond repute
Default

Quote:
Originally Posted by mearjun View Post
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.
Attached Files
File Type: fsm tableexport.fsm (62.4 KB, 38 views)
  #17  
Old 08-05-2014
Matt Long Matt Long is offline
FlexSim Development
 
Join Date: Apr 2012
Posts: 66
Downloads: 37
Uploads: 29
Thanks: 2
Thanked 150 Times in 40 Posts
Rep Power: 192
Matt Long is a glorious beacon of lightMatt Long is a glorious beacon of lightMatt Long is a glorious beacon of lightMatt Long is a glorious beacon of lightMatt Long is a glorious beacon of light
Default

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.
The Following 6 Users Say Thank You to Matt Long For This Useful Post:
Tom David (08-06-2014)


Thread Thread Starter Forum Replies Last Post
Excel table import using user commands ardodul Q&A 6 04-27-2012 02:04 PM
Questions concerning table, chart and excel Hao Zhou Q&A 0 05-19-2011 05:21 AM
Exporting Global Tables with Excel 2007 david_white Q&A 8 11-11-2008 10:34 AM
Can I make a column of a global table to type table? qin tian Q&A 0 10-01-2008 09:27 PM
Excel and global tables Bill Nordgren Q&A 2 10-15-2007 04:36 AM


All times are GMT -6.
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Copyright 1993-2018 FlexSim Software Products, Inc.