Passing data to an open workbook from Lotus Notes

[SunTzuComm]

This is not about exporting Notes data to Excel; it's about updating an
existing Excel workbook with data from Notes, which ought to be a similar
process.

Here's the scenario:

(1) User opens a Lotus Notes document that contains a Rich Text field that
contains an attached (not embedded) Excel file.

(2) User right-clicks the attached Excel file and selects Edit, which launches
an Excel session using the attached file as the active workbook.

(3) User switches to the Notes document and clicks an action button that
retrieves customer information (from a different Notes database) and that moves
it automatically into specific ranges in the active workbook in the Excel
session.

(4) User switches to the Excel workbook, double-checks the customer
information, and makes any desired changes to the workbook.

(5) User saves the Excel workbook and closes it, returning automatically to
the Notes document.

(6) User saves the Notes document and closes it.

(7) Life is good.

Here's the problem:

Can anyone tell me how to move the data from Notes to Excel in Step (3)?

Here's what I know does NOT work:

Notes Field Exchange (F/X). This requires the Excel file to be embedded in the
Notes document, and I can't do that for technical reasons. Plus, it wouldn't
work for the Excel files attached to existing Notes documents, since they lack
the custom file properties for the target ranges.

Passing the data via a text file. Okay, this works, but it requires the user
to run an Excel macro to retrieve the text file to populate the target ranges.
If I ask users to go to that much trouble, they'll just enter the customer
information manually into the workbook, along with enough typographical errors
to make a text search of the Notes documents pointless. Also, the workbooks
attached to existing Notes document won't contain the macro.

Using the Win32 API CreatePipe function. Same problem as passing data via a
text file: The user needs to run an Excel macro to retrieve the data.

I know I can open the Excel file in Notes, modify it, and reattach it to the
Notes document without the user seeing it. Frankly, that's what I'm trying to
avoid. We do that now in some cases, and the process creates its own set of
problems -- like lost attachments. Plus, we're trying to get users into the
habit of right-clicking an attachment when they want to view or edit it.

There MUST be a way to do this! LotusScript is almost identical to VB, and
Notes has full access to the Excel object model. Any help would be
appreciated.

Thanks!
Wes

 

[SunTzuComm]

I found the solution. Here's the subroutine for processing the click event of
the Lotus Notes action button.

Sub Click(Source As Button)
Dim xlApp As Variant
Dim xlWkb As Variant

Set xlApp = GetObject("", "Excel.Application")
Set xlWkb = xlApp.Workbooks(1)

With xlWkb.Sheets(1)
..Range("J3").FormulaR1C1 = "Customer Name Here"
..Range("J4").FormulaR1C1 = "Customer Address Here"
End With
End Sub

I knew it would be something easy.