Skip navigation
1400 Views 10 Replies Latest reply: Jan 5, 2005 12:00 AM by A.Non RSS
Copper 5 posts since
Jan 2, 2005
Currently Being Moderated

Jan 2, 2005 12:00 AM

array to Excel via OLE automation GetValue

I can't seem to move data from a MathCAD v12a array back into Excel 2002 SP1 using the "GetValue" method of the OLE automation interface.

However, I can transfer single values back into Excel with no problem (such as is demonstrated in the thermocouple quicksheet example) thus:

Range("MyRange").value=MyMcadWorksheet.GetValue("MyVariableName")

But I get the following error message if I try to move an array by setting MyVariableName to the name of an array...

Runtime error 1004 "Application-defined or object-defined error"

I get the same error regardless of the size of "MyRange."

So....how do I pass an array of real numbers back to Excel using OLE automation?
  • TomGutman Silver 10,537 posts since
    Oct 22, 2006
    Currently Being Moderated
    Jan 2, 2005 12:00 AM (in response to DavidE-disabled)
    array to Excel via OLE automation GetValue
    I'm not com[letely sure of this, but I don't hink that the object you get for a Mathcad array is suitable for assignment to a range. I think you have to program a loop, extracting the individual scalars and assigning them to individual cells.

    You could instead use an Excel component. That will assign an array to a range of cells.

    Tom Gutman
  • TomGutman Silver 10,537 posts since
    Oct 22, 2006
    Currently Being Moderated
    Jan 3, 2005 12:00 AM (in response to DavidE-disabled)
    array to Excel via OLE automation GetValue
    Looks like you have run into another MC12 bug. The GetElement method is returning some sort of unsupported object. You need to report it to Mathcad, in case they run out of things to fix in MC12.

    In the meantime, I'll make my usual recommendation of going back to MC11. Your macro works fine there (with the slight problem that you are trying to index the cells with a zero based index, and Excel likes one based indices).

    If that is not possible, I don't know what you can do. There is some sort of Mathcad add-in for XL (available from Mathsoft), but I don't know if that will provide any better interface. You could try reversing the applications, embedding an XL component in Mathcad. There are some older OLE interfaces available in Mathcad, but I don't know how you would make sure to activate one of those, nor whether they actually still work in MC12.

    Tom Gutman
    • A.Non Diamond 10,197 posts since
      May 11, 2010
      Currently Being Moderated
      Jan 5, 2005 12:00 AM (in response to TomGutman)
      array to Excel via OLE automation GetValue
      On 1/3/2005 7:32:37 PM, Tom_Gutman wrote:
      >Looks like you have run into
      >another MC12 bug. The
      >GetElement method is returning
      >some sort of unsupported
      >object.

      I've spent a while playing around with this, and as far as I can determine it's returning a Data Access Object. Except of course it really isn't, it just thinks it's a Data Access Object. Here's a variation of the code that is decoupled from Excel:


      Set objMCApp = CreateObject("Mathcad.Application")
      Set objMCWks = objMCApp.Worksheets.Open("D:\Mathcad\automation_test.mcd")

      'This works
      Set objMCMtx = objMCWks.GetValue("XY")

      MsgBox VarType(objMCMtx) & " " & TypeName(objMCMtx)
      MsgBox "Real part " & objMCMtx.Real & vbCRLF & "Imaginary part " & objMCMtx.Imag

      Set objMCMtx = objMCWks.GetValue("XZ")

      'This is OK. It's a ImatrixValue object in VB and VBscript
      MsgBox VarType(objMCMtx) & " " & TypeName(objMCMtx)

      'This works in VBscript, but does not work in VBA. In VBA VarType returns the code for a Data Access Object.
      Set ElementValue = objMCMtx.GetElement(1, 1)
      MsgBox VarType(ElementValue) & " " & TypeName(ElementValue)

      objMCWks.Close (mcPromptToSaveChanges)


      Curiously, using Mathcad 12, when this code in run through a VBscript interpreter it works fine. When it is run through a VBA interpreter, it fails. I can't currently test it in version 11, but if someone could verify that it works in VBA with Mathcad 11 I would appreciate it. The other thing that would be useful would be if someone that has version 12 and VB (as opposed to VBA) installed could test it to see what happens.

      Richard

More Like This

  • Retrieving data ...

Bookmarked By (0)