1 Reply Latest reply on Jul 26, 2018 3:06 PM by gregor

    Write process variable to an embedded Excel file


      I am trying to write a variable from a 'Value' into an embedded excel file.


      I have extracted the variable into the VBA environment however whenever i try to use the 'set' command with the OLEObject VBA returns the error (Run-time error '-2147467259 (80004005)': Automation Error, Unspecified Error'). See my code below.


      Sub UpdateTable()


      Dim PumpHead As String

      Dim PumpFlow As String


      Dim PumpHeadVal As Value

      Dim PumpFlowVal As Value

      Dim ObjVal As Object


      Dim vDate As Date

      Dim vStatus As Long


      Set PumpHeadVal = ThisDisplay.PumpHeadValue

      Set PumpFlowVal = ThisDisplay.OilFlowValue


      PumpHead = PumpHeadVal.GetValue(vDate, vStatus)

      PumpFlow = PumpFlowVal.GetValue(vDate, vStatus)


      MsgBox Application.ActiveDisplay.OLEObjects.Item(1).Height

      'This is used to test the reference (1) to the OLE object and returns a value

      Set ObjVal = Application.ActiveDisplay.OLEObjects.Item(1).Object

      'this is the point where the program fails no matter what i try it will not let me set my object as the active OLEObject

      ObjVal.worksheets(1).range("A1").Value = PumpHead



      Set ObjVal = Nothing


      PIObjLib.Application.ActiveDisplay.Modified = False


      End Sub

        • Re: Write process variable to an embedded Excel file

          Hello Brett,


          Many resources refer to error number -2147467259 (80004005) as unknown system error but if I recall correctly, it's indicating 'access denied'.

          I admit that I have adopted the majority of below code from a case which I found in our (internal) Technical Support database.


          Dim WithEvents oContextHandler As ContextHandler
          Dim oleObj As OLEObject
          Dim xlBook As Workbook
          Dim xlSheet As Worksheet
          Private Sub Display_Open()
          End Sub
          Private Sub oContextHandler_ContextChanged(FromDisplay As Display, FromContextHandler As ContextHandler)
                xlSheet.Cells(1, 1) = oContextHandler.CurrentContext(ThisDisplay)
          End Sub
          Private Sub CommandButton1_Click()
                Set oContextHandler = Application.ContextHandlers("E")
                Set oleObj = ThisDisplay.OLEObjects(1)
                Set xlBook = oleObj.Object
                Set xlSheet = xlBook.ActiveSheet
                xlSheet.Cells(1, 1) = "Hello World!"
          End Sub


          Edit: A reference (Tools -> References ...) to Microsoft Excel XX.X Object Library is required where XX.X refers the library version installed.