?
Solved

Manipulating an Excel OLE object in Notes

Posted on 2003-11-17
13
Medium Priority
?
1,593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 31

Expert Comment

by:qwaletee
ID: 9765595
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
ID: 9765665
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
ID: 9765727
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9765753
0
 

Author Comment

by:stanstan30
ID: 9766871
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
ID: 9766994
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9767401
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
ID: 9767758
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
ID: 9768070
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
ID: 9771653
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
ID: 9775055
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 2000 total points
ID: 9776854
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
ID: 9781820
Qwaletee,
That was the answer.  
Thanks for all your help!!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, 9 hours left to enroll

765 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