Dear Colleagues
I am currently working on my PhD in Chemical Engineering and using Excel in conjuction with Aspen Hysys Process Simulator, which is a licensed software developped by AspenTech. I have written macros in Excel Macro 4.0 language to transfer data to Hysys, to receive data from Hysys, and to do calculations in Excel with the data received from Hysys. Hysys has a special interface called Aspen Simulation Workbook (ASW) to execute all communications between Hysys and Excel. Once Hysys is installed in a computer, ASW is automatically installed in Excel.
I would like to repeat the following algorithm 50 times in a loop.
- Transfer input data from Excel to Hysys
- Wait till Hysys completes calculations with the set of input data.
- Transfer the calculated data from Hysys to Excel
- Perform some calculations in Excel
- Tabulate the results of calculations in an Excel Table
- Repeat the steps 1 through 5 above 50 times.
When I run each loop manually one by one, I have no problem. When I set up this algorithm, Excel runs through all loops, but does not update the new data coming from Hysys.
My questions are:
- What am I doing wrong?
- Is there any way that you recommend?
In case you are not familiar with Hysys, I am sending you a sample Excel file attached to this message to illustrate the problem. If I can solve the problem with this file, I believe I will be able to solve my problem above.
The file “Test” is doing the following.
- On Page2 of the file I try to get the current Central European Time (CET) from the web. I set the clock so that Excel updates the data every 1 minute.
- The Excel macro, on Macro1 tab, runs a command macro called GetCET. GetCET calculates the current time and receives CET from Page2.
- The drive macro IncrementTime runs GetCET every 1 minute and records the current time and CET on Page1
- When I run these macros, as you can see in Page1, the current time advances 1 minute at a time, while the CET remains the same although Excel should have updated the CET every minute.
Now can you comment on what I am doing wrong?
Can't use the file you sent, macro 4 functions are blocked on my system. Anyway, they will removed from excel, why concentrating on a solution that will no longer work someday?
I suggest migrating from macro4 to a vba solution.