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

    Write process variable to an embedded Excel file

    BrettCremin

      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

       

      ObjVal.worksheets(1).calculate

      Set ObjVal = Nothing

       

      PIObjLib.Application.ActiveDisplay.Modified = False

       

      End Sub

        • Re: Write process variable to an embedded Excel file
          gregor

          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.