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 > Tips and Tricks
Downloads

Tips and Tricks Share helpful modeling ideas

  #1  
Old 08-06-2009
Ben Wilson's Avatar
Ben Wilson Ben Wilson is offline
Flexsim Consultant, Super Moderator
 
Join Date: Jul 2007
Posts: 82
Downloads: 352
Uploads: 6
Thanks: 30
Thanked 159 Times in 43 Posts
Rep Power: 256
Ben Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of light
Default Writing a function to Excel

Attached you'll find a .t file containing the user command colNumToExcelCol().

This user command is used to convert a column number (eg. 34) to an Excel column name (eg. AH).

Why would you need to do such a thing? If you are exporting data to an Excel file, and would like to have Excel do the work of sums, multiplications, or whatever, then you can use Flexsim to write a function to a cell. For instance, if you wrote out two cells to Excel, then wanted a third cell to be their sum, you could do the following:


excelwritenum(row, col, 123);
excelwritenum(row, col+1, 456);
excelwritestr(row, col+2,
concat("=SUM(",
colNumToExcelCol(col), numtostring(row,0,0),
", ",
colNumToExcelCol(col+1), numtostring(row,0,0),
")")
);

This is especially helpful if your column numbers may vary. In these cases you won't want to hard code Excel column names into your code. This user command saves you from having to do that.

Hopefully it will be useful to someone.

Ben
Attached Files
File Type: zip colNumToExcelCol.zip (2.7 KB, 242 views)

Last edited by Ben Wilson; 08-06-2009 at 12:31 PM.
The Following 8 Users Say Thank You to Ben Wilson For This Useful Post:
Tom David (08-07-2009)
  #2  
Old 08-10-2009
AJ Bobo's Avatar
AJ Bobo AJ Bobo is offline
Flexsim Senior Developer
 
Join Date: Jul 2007
Posts: 108
Downloads: 8
Uploads: 0
Thanks: 23
Thanked 89 Times in 41 Posts
Rep Power: 221
AJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the rough
Default

Ben,

In Flexsim's Excel communication DLL there are functions that already do this. Unfortunately, they are not currently exposed to the user. I've sent an email to the development team asking to make standard Flexscript commands for this functionality.

On another note, my conversion functions can only handle the number-to-string conversion for up to 256 columns. It can be changed to handle more than that if people need it. Versions of Excel prior to 2007 were limited to 256 columns. Now, with the .xslx file format, Excel can handle around 1000 columns (I think). How many of you out there are using that format, and do you really need that many columns?

A.J.
  #3  
Old 08-10-2009
AJ Bobo's Avatar
AJ Bobo AJ Bobo is offline
Flexsim Senior Developer
 
Join Date: Jul 2007
Posts: 108
Downloads: 8
Uploads: 0
Thanks: 23
Thanked 89 Times in 41 Posts
Rep Power: 221
AJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the rough
Default

By the way, my estimate on the number of columns that Office 2007 can support was way off. It isn't 1000. It looks like it's actually 16384. Anybody out there actually want to use that many with Flexsim?

A.J.
  #4  
Old 08-10-2009
Ben Wilson's Avatar
Ben Wilson Ben Wilson is offline
Flexsim Consultant, Super Moderator
 
Join Date: Jul 2007
Posts: 82
Downloads: 352
Uploads: 6
Thanks: 30
Thanked 159 Times in 43 Posts
Rep Power: 256
Ben Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of lightBen Wilson is a glorious beacon of light
Default

You may want to update your underlying code to work for any number of columns. This will make it future proof, and the code is really quite simple. You can check out my user command as an example.

It just seems to me that if the functionality is going to be exposed for general use, it ought to work for general use.
  #5  
Old 08-10-2009
AJ Bobo's Avatar
AJ Bobo AJ Bobo is offline
Flexsim Senior Developer
 
Join Date: Jul 2007
Posts: 108
Downloads: 8
Uploads: 0
Thanks: 23
Thanked 89 Times in 41 Posts
Rep Power: 221
AJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the rough
Default

Ben,

When I wrote it, supporting 256 columns was all it needed to do. But it should be easy to change. Your approach was a bit different than mine, but I think I like yours better. Thanks.

A.J.


Thread Thread Starter Forum Replies Last Post
Excel 2002 vs. Excel 2007 Nico Zahn Q&A 2 04-27-2009 01:47 AM
Exporting Global Tables with Excel 2007 david_white Q&A 8 11-11-2008 10:34 AM
While functions Xavier Jackson Q&A 12 08-12-2008 01:32 PM
Automated runs / Exporting mdb file.. Nico Zahn Q&A 14 12-17-2007 11:26 AM
BasicFR Advanced Functions Cliff King Q&A 0 11-13-2007 08:02 PM


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.