I have Rich text field with Excel file as Embedded object. And I am updating that Excel object in backend document using Lotus script. It contains preformatted sheet “Whatever” where values are written to fixed cells.
Set xlObject = rtItem.GetEmbeddedObject( “ExcelOLE” )
Set xlHandle = xlObject.Activate ( False )
Set xlSheet = xlHandle.Application.Workbooks(1).Worksheets(“Whatever”)
xlSheet.Activate
With xlSheet.Cells(4, 4)
.Value = varMyValue
End With
……
And it works fine while Excel is not opened or no workbook is opened.
But as soon as any excel workbook is opened I am getting error because of course worksheet “Whatever” is not found in Workbook(1), which in this case is some other workbook opened by user.
I can’t use ActiveWorkbook property, because object user interface is hidden. And ThisWorkbook also in unusable because it can be used only from inside Microsoft Excel
I would appreciate any idea how could I refer exactly to that workbook opened by xlHandle?
Does your script always assume it is going to work on the first worksheet in the Worksheets collection? If so, then reference it by its index number, like you do with Workbooks, like so:
set xlSheet = xlHandle.Application.Workbooks(1).Worksheets(1)
That should work.
Now if you don’t think it is going to be the first one, you’re going to have some trouble…
No, I can’t guarantee that workbook is the first one because if there are any Excel files opened by the user, then OLE workbook is some n-tx workbook …
However, the solution for my case was pretty simple
lastWkbN = workbooks.count
It returns count of active workbooks and there is a good chance that workbook opened by LS is the last one. This script works on user machine and I don’t expect that any other workbook instance might be opened/closed in that instant moment between
Set xlObj = xlOLE.Activate ( False )
lastWkbN = xlObj.Application.Workbooks.Count
and further I reference sheet this way
set xlSheet = xlHandle.Application.Workbooks( lastWkbN ).Worksheets(“whatever”)