Execute Excel Macro Through DIAdem

[GT_Mark]

Hi All,

  I am currently trying to run a script in DIAdem which exports certain material properties to an Excel Macro-enabled workbook.  Inside the Excel workbook the user executes a macro to perform some calculations.  The results of that calculation will then be re-imported into a DIAdem report.  I have the import/export data part down, but the million dollar question is how can I execute an Excel macro from DIAdem's VBScript?

  I know some are going to say this makes no sense, just do the calculation in DIAdem.  I know this, but the file/calculations are an industry accepted toolbox and it must be done this way. 

  Thanks in advance and here is some dumbed down code to showcase the example:

Option Explicit  'Forces the explicit declaration of all the variables in a script.

Dim excel
Dim excelbook
Dim a
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Set excelbook=Excel.Workbooks.Open("My File")

excelbook.Sheets("InputResults").range("B5").value = Data.Root.ChannelGroups(ChanGrp#).Properties("myProp").Value

'Call 'My_Macro' !!!!!!!!!!!!!!!!!!!!

Msgbox("Result is: " & excelbook.Sheets("InputResults").range("B5").value)

[winner · Accepted solution]

Hi GT_Mark

You simply can use the methode Run

Call Excel.Run("MyMacro")

Of course you have to make sure that you refer only to data that are available in Excel.

To obtain CodeCompletion for Excel in the script editor select Microsoft Excel x.x Objects Library in the "Registed Type Libraries" dialog box. The x.x value depends on your installed Excel version.  To open this dialog box, select Script»Type Libraries in DIAdem SCRIPT.

Hope this helps

Winfried

[GT_Mark]

That did the trick, winner, thanks for your prompt response!