Cancel
Showing results for 
Search instead for 
Did you mean: 

Run Excel macro via PlantSim

Creator
Creator

Dear PS community,

 

I am looking for a way to start a Excel macro via SimTalk in Plant Simulation. I already integrated the Excel file as a ActiveX object and transferring data works fine as well. But I don´t know how to run a macro via SimTalk.

 

As another approach I tried to run the macro via DDE and after a few succesful runs it stopped working.

 

So this is myapproach to run "Makro1" in "Tabelle2" with DDE. I already tried both, excel file open and excel file closed.

 

var kanal: integer

kanal := ddeconnect("Excel","Tabelle2")
str:= "[Run(" + Chr(34) + "Makro1" + Chr(34) + ")]"
DDEExecute(kanal,str,1000)
ddeDisconnect(kanal)

 

As I already connected my excel file with PS via ActiveX, I think it would be much better to run the macro in SimTalk without DDE, but unfortunately I don´t know how.

 

Many thanks in advance.

12 REPLIES

Re: Run Excel macro via PlantSim

Phenom
Phenom

Next  approach: the com interface.

 

Try this code with a macro ("macro1") created in the excel  file "xxx.xlsM"

 

var excel :any 

	excel:=CreateComObject("Excel.Application")

	excel.visible:=true
	excel.Workbooks.Open("c:\temp\xxx.xlsM")
	excel.Application.run("xxx.xlsM!macro1");

Re: Run Excel macro via PlantSim

Creator
Creator

Thank you for the fast reply. Now, the macro partly runs well. However, there is a new problem appearing concerning the VBA code for the excel solver.

 

My overall intention is to export data from PlantSim to Excel, solve the given optimization problem via excel solver and import the data back to PlantSim. At the moment, it runs the macro but not the part with the excel solver code.

Again, I tried both approaches, the ActiveX (I just added excel.application.run("xxx.xlsm!macro1")) and the COM interface, but for both approaches it only runs the macro without solving the problem.

 

If I run the macro in excel by hand, it solves the problem in an instance. This is my code in VBA. The table which should be optimized is attached.

 

Sub MakroNanoBerechnung()

Dim LZ As Long

LZ = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row

'Solver-Erstwerte eintragen
Tabelle1.Range("G2").ClearContents
Tabelle1.Range("G2").Formula = "=SUMPRODUCT(B2" & ":B" & LZ & ",C2" & ":C" & LZ & ",E2" & ":E" & LZ & ")"
Tabelle1.Range("H2").ClearContents
Tabelle1.Range("H2").Formula = "=SUMPRODUCT(D2" & ":D" & LZ & ",E2" & ":E" & LZ & ")"

'-------------------------------------------------
'Macro stops running
'-------------------------------------------------

Tabelle1.Select
'Optimale Lösung finden
SolverReset
SolverOptions Precision:=0.001
SolverOptions Iterations:=1000
SolverOptions IntTolerance:=0.001
SolverOK setCell:=Range("G2"), _
 maxMinVal:=1, _
 byChange:=Range("E2:E" & LZ)
SolverAdd cellRef:=Range("H2"), _
 relation:=1, _
 formulaText:="100"
SolverAdd cellRef:=Range("E2:E" & LZ), _
 relation:=5

SolverSolve userFinish:=True

'-------------------------------------------------
'Macro starts running again
'-------------------------------------------------

Tabelle1.Range("G9") = 213
Tabelle1.Range("G10") = 123213

End Sub

 

Many thanks in advance for your help.

Re: Run Excel macro via PlantSim

Phenom
Phenom

Set a breakpoint in the  macro a see why the solver is not working

Re: Run Excel macro via PlantSim

Creator
Creator

Hi,

 

so I set a breakpoint in the macro and tried to figure out the problem... without any success. I attached an example to make clear my point.

 

Running the solver in excel works, it solves the problem. However, if I start PlantSim and run the macro (init method), it does not solve the problem. No error message or whatsoever occurs. The macro runs just fine, but actually does not solve the problem.

 

Maybe it´s an issue with the licence? I am using the educational version.

 

Thank you for your help.

 

 

 

 Sub Makro1()
Tabelle1.Select
'Optimale Lösung finden
SolverReset
SolverOptions Precision:=0.001
SolverOptions Iterations:=1000
SolverOptions IntTolerance:=0.001
SolverOK setCell:=Range("F2"), _
 maxMinVal:=1, _
 byChange:=Range("D2Smiley Very Happy11")
SolverAdd cellRef:=Range("G2"), _
 relation:=1, _
 formulaText:="100"
SolverAdd cellRef:=Range("D2Smiley Very Happy11"), _
 relation:=5
SolverSolve userFinish:=True
End Sub

 

 

Re: Run Excel macro via PlantSim

Phenom
Phenom

1. What should the result of the solver be ?

2. Check if you can run all macros on XLM workbbok.

Re: Run Excel macro via PlantSim

Creator
Creator

1. the objective is the maximization of the "value". Therefore, the solver should pick those jobs that maximize this value under the condition of the sum of the job sizes beeing smaller or equal to 100. In this case, the solver picks the jobs 1,3, 5-9. Picking means switching the selection from "0" to "1".

 

If I run the macro via PlantSim, the solver does not switch these values and the value of "0" for each job remains. Running the solver from excel solves the problem and the named jobs are picked (values are switched from "0" to "1").

 

2. All other macros are running on the .xlsm workbook.

 

 

 

Re: Run Excel macro via PlantSim

Phenom
Phenom

Have a look at xxx.spp and xxx.xls ( rename it to xxx.xlsM)

 

Manual solver and macro2 triggering through xxx.spp give the same result

 

 

Re: Run Excel macro via PlantSim

Creator
Creator

First of all, thank you for your patience.

 

By now, I think it´s an issue with the licence. I had a look at your files with the same outcome... manual solving worked (file "3") and triggering macro2 from xxx.sps did not work (file "1" and "2"). I used your files xxx.spp and saved xxx.xls to xxx.xlsm.

 

I´ll try the research licence tomorrow.

Re: Run Excel macro via PlantSim

Phenom
Phenom

I don't think it has something to do with the Plant Simulation license.

 

I guess it could have something to do with certain access rights of your excel (files/folders ?)

 

In any case I tested your attached files and the solver seems to create results ( man. and via spp macro )