11 Replies Latest reply: Feb 4, 2015 8:07 PM by ptc-6878367 RSS

    array to Excel via OLE automation GetValue

    Copper
      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?
        • array to Excel via OLE automation GetValue
          TomGutman Silver
          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
          • array to Excel via OLE automation GetValue
            Copper
            Tom, I'm sorry, but I should have been more clear. That's exactly what I did. Here's the EXCEL 2002 VBA code... Everything works except for the GetElement line.


            Sub daetest()
            'This did not work with Mathcad v12
            'Does work with v 12a...except for the GetElement(i,j) method

            'Activate and get the Mathcad object
            Dim obj As OLEObject
            Set obj = ActiveSheet.OLEObjects(1)
            obj.Activate
            Set Mcws = obj.Object.Worksheet

            'Take data from the single cell range and transfer to the worksheet
            singlecell = Range("singlecell").Value
            Mcws.SetValue "Xsinglecell", singlecell

            'Try the same with an array
            Dim multiplecells(3) As Double
            For i = 0 To 3
            multiplecells(i) = Range("inputrange").Cells(i + 1, 1).Value
            Next i
            Mcws.SetValue "Xmultiplecells", multiplecells

            Mcws.Recalculate

            ' Populate output range with revised data
            Set XZmcad = Mcws.GetValue("XZ")
            For i = 0 To XZmcad.Rows - 1
            For j = 0 To XZmcad.cols - 1
            Range("outrange").Cells(i+1, j+1).Value = XZmcad.GetElement(i, j)
            Next j
            Next i

            End Sub



              • array to Excel via OLE automation GetValue
                TomGutman Silver
                Could you post the actual worksheet? This macro seems to depend on quite a bit of environment to work, and I'm not sure of all of it. I don't see anything wrong at first sight, but I don't work all that much with the automation interface. And I don't even know what sort of failure you are getting.

                Tom Gutman
                  • array to Excel via OLE automation GetValue
                    Copper
                    You bet. Here it is...
                      • array to Excel via OLE automation GetValue
                        TomGutman Silver
                        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
                          • array to Excel via OLE automation GetValue
                            Copper
                            Tom,

                            Thanks for your help!

                            Dave
                            • array to Excel via OLE automation GetValue
                              A.Non PTC Community Champion
                              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