Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Navigation
- Tecnomatix
- Forums
- Blogs
- Knowledge Bases
- Groups

- Siemens PLM Community
- Tecnomatix
- Plant Simulation
- Run Excel macro via PlantSim

Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Run Excel macro via PlantSim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018 10:40 AM

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 12

Re: Run Excel macro via PlantSim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018 11:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2018 02:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-20-2018 04:40 PM

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

Re: Run Excel macro via PlantSim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2018 06:46 AM

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("D211")

SolverAdd cellRef:=Range("G2"), _

relation:=1, _

formulaText:="100"

SolverAdd cellRef:=Range("D211"), _

relation:=5

SolverSolve userFinish:=True

End Sub

Re: Run Excel macro via PlantSim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2018 05:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2018 06:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2018 08:28 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2018 09:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2018 10:08 AM

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 )

Follow Siemens PLM Software

© 2019 Siemens Product Lifecycle Management Software Inc