Manipulating an Excel OLE object in Notes

If you embed it in a form, it SHOULD update the form's copy when you exit Excel.  You are seeing something different?

The easiest way to manage thsi is with Notes/FX.  Name tha Excel sheet ranges that you wish to import to Notes, use matching Notes field names, enable FX, and voila, teh docuent will contain data lists matching the Excel columns.  If you want to parcel that out to documents -- no problem.  our script will run much more efficiently if it leaves the Excel-Notes transfer to teh FX code and just has to deal with Notes; OLE automation control is fairly slow, which is what you would have to do with the Excel object model.  Not to mention that what you would be doing is re-opening the object a SECOND time at "export" just to find what the user enetered.

stan,

If you want to do that then using COM write a script in Excel file where it will parse each cell data and create notes document with appropriate data.

COM for Domino is easy to use and is similar to vb/lotus script (basically ansi basic).

~Hemanth

Here is the help documentation on how to use domino objects using COM

http://www-10.lotus.com/ldd/notesua.nsf/ddaf2e7f76d2cfbf8525674b00508d2b/6932e4fc0ea17542852569d9005bd01d?OpenDocument

Place the script in BeforeSave event of the workbook.

Here are few samples that can get you started.

http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/e8548990f6891c5d85256b7a000b1b5e?OpenDocument

http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/416a83787c011d7e8525690e00606bba?OpenDocument

Also check notes designer help on COM where you can find some simple to use examples.

Thanks for the quick response, here is some additional info

[1] Response to Qwaletee:
(a) When I access the excel via the form, make a change, and then exit the "excel" the form has the change, but if I exit the form, the changes are lost.
(b) I thought if you use notes/fx you have to point to a specific excel; probably on a local or network drive.  I don't have (or want) an excel on a network drive.  I thought that it could be stored within the nsf.

[2] Response to HemanthaKumar
(a) When using COM, does this allow me to store the excel within the nsf?  

Yes that you can control it from the vba script. For the changes to get into notes document, you should select File..Update option in the excel file menu. This is normal save when excel is opened normally instead from OLE container which is the document itself.

stanstan30,
> [1] Response to Qwaletee:
> (a) When I access the excel via the form, make a change, and then exit the
> "excel" the form has the change, but if I exit the form, the changes are lost.

The sequence would be:
* Design form with Excel template embedded as object
* Save form
* Create doc with form
* Edit the Excel object on the document (should be able to in-place edit it)
* Click outside Excel
* Edit in-place again -- you shoudl see your changes are still there
* Make another change, click outside Excel
* >>>Save the document<<<
* Close doc and re-open.  Your changes should still be there

Now, you can FX-enable it, read on


> (b) I thought if you use notes/fx you have to point to a specific excel;
> probably on a local or network drive.  I don't have (or want) an excel on a
> network drive.  I thought that it could be stored within the nsf.

FX always works with an OLE instance on the doc or on the form.  It never uses an "external file."
To set it up... most of the information is in teh Excel help file.  Look at the help topic that describes how to display workbook data n Lotus Notes views.  Here's an outline:

Embed Excel object
Right-click object, Open
Create a range name
File -> Properties -> Custom
Create a custom name property and link it to the range name; as a good practice, name it the same as the range
Save everything in Excel (save back to Notes form)
Back on the form, create a feld with the same name as the custom property
Enable FX on the form

Note: For an easy test, yuo can skip all the range naming and properties.  The Notes field Subject corresponds to the the Excel file property Subject.  you can simply verify that changing the subject field changes the subject property and vice versa.

Heman (or anyone else)
It sounds like your saying to create an excel outside of notes, and create a vb script to load it into notes.  What I'm trying to do is keep everything stored in notes (for replication purposes and to ensure only one user accesses the excel at one time)
This is what I have so far...  I created an embedded object in a form. I have a Lotus Script that parses the embedded excel which is in the notes form.  It reads the NotesUIDocument for the excel and for each row in the excel it creates a document in the nsf.  What I'm thinking, (again I'm new at this)  is to have one document that contains the embedded excel which the user can manipulate and then have a lotusscript that will take that embedded excel and create individual documents for each row in the excel.  The problem is that I need to be able to manipulate the excel, save it, look at it, all within notes and not store it on a network drive. Please advise.

Stan,

Just to make sure we are clear, for FX, you don't ATTACH teh Excel file to the form.  You EMBED it, as an OLE object.  You can do this by saving the Excel sheet as a regula XLS file, then in Notes Create -> Object, select FROM FILE, BROWSE, and select the XLS file from disk.

NO, the excel file will be embedded into the notes, but you will code the excel to record the values onto a notes document.

Try this

Create a excel document, and write a small sample script like msgbox on BeforeSave event of the worksheet. Save the file

Open the notes form and embed the Excel file.

Then try editing it by creating a doc out of the form.

Now save it and close the excel sheet.. Msgbox should popup...

Heman,
I'm not familiar with creating a vb script in excel.  I&#8217;m sure it&#8217;s easy, but I just don&#8217;t know how to do it.  I will ask one of my cohorts.

Qwaletee,
Here is what I have so far based on your step by step.  By the way, I'm using R5; hopefully the steps are similar.

 [&#8730;] Created a form with an embedded excel and saved as a document.

I created a view that will list all documents.  In this view there is one document with no fields, if I double click it opens the form.  When I double click on the excel object I get a message that indicates the &#8220;document will be open as read-only&#8221;.  At this point I can&#8217;t make changes to the document.  

I then did your notes/fx steps
[&#8730;] Embed Excel object
     - I&#8217;m assuming you want to select the excel object in the original form
[&#8730;] Right-click object, Open
[&#8730;] Create a range name
[&#8730;] File -> Properties -> Custom
[&#8730;] Create a custom name property and link it to the range name; as a good practice, name it the same as the range
[&#8730;] Save everything in Excel (save back to Notes form)
     - used the update option (no save was available) and closed the object
[&#8730;] Back on the form, create a field with the same name as the custom property (type= rich text)
[&#8730;] Enable FX on the form and saved the form
     - The disable field exchange was unchecked

I went into my view, select a document and I still get read only when I double click the excel.  

What am I missing??  Can you attach a simple nsf??

Qwaletee,
That was the answer.  
Thanks for all your help!!!!