AnsweredAssumed Answered

Write process variable to an embedded Excel file

Question asked by BrettCremin on Jul 25, 2018
Latest reply on Jul 26, 2018 by gregor

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

Outcomes