Solved

Manipulating an Excel OLE object in Notes

Posted on 2003-11-17
13
1,564 Views
Last Modified: 2013-12-18
I'm trying to create an applicatiaon in notes that gives a user the "excel" flexibilty but store the data in notes.  I'm new to notes but have had some training.  This is what I have done so far.
(1) I tried embedding an excel sheet in a form, looks great, I can double click to open it up, but when I exit the sheet (by definition) the data is not stored in the form.  
(2) I have embedded the excel in a page and get a similiar problem in that I can only open the excel as read only.
(3) I have created a form with a rich text field and then created a document with that form imbedding an excel.  The problem is how do I get at the document and have the abilty to open it up using excel within notes.

If I can get the excel stored in notes and have a way for the user to access it via excel, then I want to be able to take that embedded excel, run a script to take each row within the excel and create individual documents in the nsf.
0
Comment
Question by:stanstan30
  • 5
  • 4
  • 4
13 Comments
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
0
 

Author Comment

by:stanstan30
Comment Utility
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?  


0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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.
0
 

Author Comment

by:stanstan30
Comment Utility
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.
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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...
0
 

Author Comment

by:stanstan30
Comment Utility
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??
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 500 total points
Comment Utility
OK, you need to put the document in edit mode before activating the embedded spreadsheet.  Press control-E, or double-click an empty area of the form.  That's probably the reason no values were saved, and it would also affect FX.

You can forcethe document to always be in edit mode.  There is a checkbox in form properties, I think on teh second tab of teh form properties box.
0
 

Author Comment

by:stanstan30
Comment Utility
Qwaletee,
That was the answer.  
Thanks for all your help!!!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now