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 11-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 Using excelmacro() and Pivot Tables

One of the lesser-known commands in Flexsim is the excelmacro() command. This command allows you to send instructions to Excel that are not handled by the other Excel commands. For example, you can use it to select a group of cells in a workbook and then make the text in those cells bold.

The language that the excelmacro() command uses is known as the Excel 4.0 Macro Language. This is a language that Excel used to use, before VBA was implemented. When VBA was added to Excel, Microsoft removed the ability to create new macros with the 4.0 language, but they left the ability to run old macros. This was done so that newer versions of Excel could continue to use old workbooks.

I've attached a sample model that uses the excelmacro() command during its export process. The model is very simple, just flowitems traveling to one of three Processors. The flowitems are each assigned an itemtype between 1 and 5, and a subtype number that is between 6 and 10. Whenever a flowitem enters the Sink, a new row is added to a GlobalTable called "Results" that records the flowitem's itemtype, subtype, Processor, how long it was in the Processor and a value that is based on the run time ("EarlyTime", "MiddleTime" or "LateTime").

The data from the "Results" table can be exported to Excel. The user is prompted for a file name to save the data to. They should NOT select the file called "OutputTemplate.xls". This is an Excel file that already has the tabs that the output report needs, but no data. The user should enter the name of a new file, although they can overwrite existing ones also.

After the raw data is written to Excel, the excelmacro() command is used to format the tab that has the raw data. It is also used to create a Pivot Table that summarizes the results on a different tab. A Pivot Table is a powerful tool in Excel that allows you to sort and filter data. It will show you the number of flowitems of each itemtype that went through each Processor. It also shows the total amount of time that each Processor spent processing the flowitems, as well as the average time per flowitem. The Pivot Table allows you to filter which itemtypes, subtypes, processors, and time ranges are visible in the report.

Once all the data is written to Excel, and the Pivot Table has been generated, the excelmacro() command is used one more time to save the workbook. Flexsim has an excelsave() command, but I tend to use this system for saving workbooks because the excelsave() command does not continue to prompt you for a workbook name if you choose an existing workbook to save then choose "No" when you are asked if you want to overwrite.

I hope this small model helps you take better advantage of what Excel can do when you are using it for output. Please let me know if you have any comments or questions.
Attached Files
File Type: zip PivotTableExample.zip (44.2 KB, 286 views)
The Following 5 Users Say Thank You to AJ Bobo For This Useful Post:
Tom David (11-10-2009)
  #2  
Old 11-11-2009
Tom David's Avatar
Tom David Tom David is offline
Flexsim User
 
Join Date: Aug 2007
Location: Schwaebisch Gmuend, Germany
Posts: 430
Downloads: 157
Uploads: 47
Thanks: 486
Thanked 450 Times in 233 Posts
Rep Power: 520
Tom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant future
Default

A.J.,

Today I checked your model and it seems not to work for me.
There are different possibilities why it is not working on my side.

One reason I see is that I use the GERMAN excel version.
In your code is r and c used which is Row and Column. In German they are called z and s (Zeile and Spalte).
So I changed this in the code.

It was still not working, so I checked if the writing of the Results Table to Excel is working, which is unfortunately not working. I tried it with the Single Table Export which is working.
Later I figured out, that I am able to write to Excel with the Custom Code, but it was not saved, because the Macro for the saving is not working for me. So I add a line for me excelsave() for checking.

I tested a bit more and figured out, that the macros not getting executed. I am using Office 2007.
So another reason could be the 2007 Excel Version.

Which Excel Version do you use? Or should it work with every Excel Version, which is the idea of using the old Excel 4.0 Macro Language?
If I compare the commands you are using SELECT … it looks in my macros different if I record a macro.

I am not an Excel-Expert, so maybe that another possibility, why it is not working.

Anyway, I will do some more testing (changing the macro commands) if I have time. If anyone has an idea why it is not working on my computer, I would appreciate any hints.
__________________
tom the (A)tom: "We have solved our problems ... now we have to fight the solutions."
The Following User Says Thank You to Tom David For This Useful Post:
Jason Lightfoot (11-11-2009)
  #3  
Old 11-11-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

I knew about the row/column character issue. I just forgot to mention that in my original post. Sorry. For anyone out there that's using a non-English version of Excel, all of the cells references in the macros are in R1C1 notation. You'll need to change all of the r's and c's in the excelmacro() calls to whatever characters are correct for your language.

I'm using Excel 2007. I've also tried this model on Excel 2003 (I think - the computer belonged to someone else). It worked there too. What are your Excel security settings?
  #4  
Old 11-12-2009
Tom David's Avatar
Tom David Tom David is offline
Flexsim User
 
Join Date: Aug 2007
Location: Schwaebisch Gmuend, Germany
Posts: 430
Downloads: 157
Uploads: 47
Thanks: 486
Thanked 450 Times in 233 Posts
Rep Power: 520
Tom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant futureTom David has a brilliant future
Default

A.J.,

I spoke yesterday with Jason about it and he sent me the information he found about the Security Settings. It is a change in the registry, what I tested, but did not change anything on my side.
HKEY_LOCAL_MACHINE\SOFTWARE\Micorsoft Office\11.0\Excel\Security DWORD XLM Value 1.

Because I did not know a lot about the Excel security settings, I tried to find out, what are my settings. I change them that everything is possible (VBA and Macros), but the macros are still not executed and there even the raw data are not saved into the Excel file.

I am not sure why it is not running on my side.
Maybe it would be helpful if more community users could give feedback if it is working in their case or not.

Operating system is XP and I am using Excel 2007 German version. – Not working.
__________________
tom the (A)tom: "We have solved our problems ... now we have to fight the solutions."
  #5  
Old 11-13-2009
Stephan Korte Stephan Korte is offline
Flexsim User
 
Join Date: May 2009
Posts: 3
Downloads: 9
Uploads: 0
Thanks: 3
Thanked 2 Times in 1 Post
Rep Power: 0
Stephan Korte has a spectacular aura aboutStephan Korte has a spectacular aura aboutStephan Korte has a spectacular aura about
Default

I think the problem is not the R1C1 reference for the cells.

Im using German Excel Version too and programming in VBA a lot, while using the R1C1 reference doesnt make any trouble.

Dont know about Excel-Makro-Language at all.

Greetings
Stephan
The Following 2 Users Say Thank You to Stephan Korte For This Useful Post:
RalfGruber (11-15-2009)
  #6  
Old 12-15-2011
Luciano Luciano is offline
Flexsim Distributor Italy
 
Join Date: Oct 2007
Location: Cuneo (IT)
Posts: 22
Downloads: 66
Uploads: 0
Thanks: 31
Thanked 27 Times in 9 Posts
Rep Power: 170
Luciano will become famous soon enough
Default

Hi Phil,

In these days I tried to use excelmacro() with Excel 2010 in Italian but I experiment the same problem that Tom with its Excel 2007 in German in 2009
Is it possibile to verify why the excelmacro() doens't work? (the flexsim system console doesn't show any message)

Thanks.
Luciano.
  #7  
Old 12-15-2011
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

Luciano,

We've recently discovered that the Excel Macro Language (which is different from VBA) doesn't always work the way you expect in non-English versions of Excel. Sometimes I think it's related to the R1C1 issue, and sometimes I think the actual commands are different. There's pretty good documentation for the macro language in English (http://support.microsoft.com/kb/128185), but you'd have to look to see if there's similar documentation for the Italian version of Excel.

Something else that I've started using when possible is a call to a VBA function from the excelmacro() command. You can't pass any parameters to VBA functions or get return values from them, but you can generally call non-parameterized functions. The command for it (at least in English) is:
Code:
excelmacro("RUN(\“ThisWorkbook.MyFunction\”,false)");
-A.J.
The Following 2 Users Say Thank You to AJ Bobo For This Useful Post:
RalfGruber (12-15-2011)

Tags
excel, excelmacro, pivot table


Thread Thread Starter Forum Replies Last Post
Automatically generated global tables Sebastian Dransfeld Gripes and Goodies 1 09-01-2008 12:44 PM
Updating Tables With A GUI Xavier Jackson Q&A 4 07-08-2008 01:14 AM
Search box in Time Tables etc. Nico Zahn Gripes and Goodies 9 06-16-2008 09:57 AM
Excel and global tables Bill Nordgren Q&A 2 10-15-2007 04:36 AM
Create tables on the fly AlanZhang Tips and Tricks 0 08-24-2007 06:01 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.