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.

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.

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?

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.

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

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.

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:

excelmacro("RUN(\“ThisWorkbook.MyFunction\”,false)");

-A.J.