Working with Excel through OLE

HCL Domino Domino Forum
1

Hi,

I’m working on some code in LotusScript to gather some data from a database, send it to Excel and have the user interact with an Excel userform, and then have the results returned to my script. The userform is stored in an Excel file on my computer. Everything works OK, but the problem is that when I open the userform, the user has to hit Alt-Tab to see it. How can I programmatically bring the userform to the front of the screen? Also, once the user clicks OK on the userform, ideally the focus should return to Lotus, but instead it goes to Excel.

Any ideas on bringing Excel to the front, and then bringing Lotus back to the front?

Thanks!

Here’s the part of my code that deals with Excel:

Dim xlapp As Variant

Dim retval As Variant

Set xlapp = GetObject(“”,“Excel.Application”)

xlapp.workbooks.open(“C:.…\To Be Opened By Lotus.xls”)

xlapp.run “ShowUserform”, retval 'retval is passed ByRef and holds the return value

xlapp.workbooks(“To Be Opened By Lotus.xls”).close

Msgbox retval(0)

2

Subject: Working with Excel through OLE

xlapp.Visible = True
3

Subject: RE: Working with Excel through OLE

I noticed on one app that even after Visible was set to true that the Excel window would not get focus. I then tried putting a message box at the end, and that strangely showed the Excel window, but then left the message box when the user returned to Notes. So I tried issuing a non-sensical command such as setting x to a string, and that works just as well. (Don’t blame me for this quirky behavior. I’m only the messenger.)

'put cursor on F2

oWs.Range(“F2”).Select

obj.Visible = True

'issue any command outside the Excel app

Dim x As String

x = “do anything to return to Excel”