Application with Embedded Excel workbooks - how do you update the design without losing all the old workbooks?

Posted on 2004-04-23
Last Modified: 2013-12-18

I have developed an application that uses embedded Excel workbooks & Notes/fx or field exchange. The workbooks are invoices, they work really well, & Notes summarises all the totals etc. nicely in the Views.

This is all great, except for one thing: if I ever change the design of the workbook, then from then on in, I can't see any of the old invoices any more! I have the empty workbook embedded on the Form itself, with the option to move to the Body field on creation. After upgrading the database to a newer design (which includes an updated workbook), when opening existing invoice documents, I only ever see the new blank workbook, rather than the existing workbook embedded in the document.

Can anyone explain what is happening here? How does Notes compare the version of the embedded file in the Form & the document? How does it know there's a new workbook on the Form now? And how do I stop this from happening?

I would expect it to show me the existing workbook on existing documents, & the new workbook on new documents, but this just doesn't happen!

Any thoughts would be appreciated, as I am still having to use a really old design, rather than the latest one I've been working on, so all these improvements are wasted if I can't use the new design.

Question by:PaulCutcliffe
  • 5
  • 3
LVL 24

Expert Comment

ID: 10899185
When you change the design of the workbook, you have to also take care of the fx fields in the workbook. The best option would be to take a copy of the existing workbook and modify it and make sure that you don't remove any fx fields.

To keep old documents with old workbook .. keep the old form. And design a new form which will be picked by the new documents.


Create two workbook tabs and define it as old format and new format, so the user can switch to what he/she wants

LVL 31

Expert Comment

ID: 10899462
Did you have "store form in document" set up?  Then it would definitely work.  If not, you have to make sure you new sheet is completely compatible with your old.  It sounds like you are using FX to exchange data between the sheet and the form.  If you changed field names on EITHER the sheet OR the form, BUT NOT BOTH, then there would be a mismatch, and data would not go through.

Author Comment

ID: 10899590
I had thought of doing something like that (having two forms, or more likely, embed the workbook on a computed subform whose formula includes a Version number from the Invoice workbook) as a last resort, but figured that I must be doing something wrong for this to be required. Is that not the case? Does this always happen whenever you embed an OLE server into a Notes form? There's no known issue with R6.0.1 in this respect?

The new workbook, by the way, IS a slightly modified version of the old one, & all the Notes/fx fields remain. I designed the changes so that they would be completely compatible with the existing documents. Don't get me wrong, however; I know Notes can't see there's a new workbook, then magically enter all the data from the old one into the new one, I just figured that if you opened an existing document with a workbook embedded in it, you'd see that embedded (old) workbook, & for newly created documents with no existing workbook in the document, you'd see the (new) workbook embedded in the Form, a modified copy of which would then move to the document.

So, am I expecting too much here? Do I really need to split the form as you suggest?

Thanks for your help,


Author Comment

ID: 10899626
To qwaletee: No, I didn't store the Form in the Document, but I did have the option to store the OLE object in a field.
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).


Author Comment

ID: 10899637
Wait! I thought I had the option to store the OLE object in a field set, but I haven't - that's probably the problem! I'll have a look & let you know...
LVL 24

Expert Comment

ID: 10899907
If you can dynamically upload the ole object into a field then it solves the problem. Yoiu just track what is to be loaded and upload .. For that you have to keep seperate documents which will hold the attachments of the old and new versions and you embed the objects accordingly for old and new documents.

Using subforms would be a better and easy soln in this case.


Author Comment

ID: 10900210
Sorry HemanthaKumar, I don't understand your last posting - what do you mean by 'dynamically upload the ole object into a field' - do you mean manually copy the completed (old-style) workbook into the respective Notes document? Also, what do you mean by 'keep seperate documents which will hold the attachments of the old and new versions and you embed the objects accordingly for old and new documents'?

LVL 24

Accepted Solution

HemanthaKumar earned 500 total points
ID: 10900463
Let me detail..

1. Create a form which will have one field as identifier and other as richtext field .. this field will hold the format of the workbook
2. Compose doc out of this form and attach the workbook and name it as Ver1 and save , similarly do it for ver2
3. Now in your documents where fx is enabled create a richtext field which will detach the attachments from the above documents appropriately and embed it into this richtext field.. That is dynamically uploading the objects based upon the document versions.


Author Comment

ID: 12168999
Sorry, I'd forgotten about this issue, having been totaly confused by it. I have since tried updating the design of the spreadsheet again, & just like I originally thought it would, Notes simply used the new design on new docs, & the design of the existing embedded file for the old ones, so I can't see what the issue was - perhaps my Notes/Office installation at the time (think it's been rebuilt/upgraded since then) was messed up.

Anyway, it all seems to work fine now. Sorry to have kept this open for so long.

I'm gonna accept HemanthaKumar's Comment, as he's always such a helpful chap & deserves the points.


Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

22 Experts available now in Live!

Get 1:1 Help Now