Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2004-04-23
Medium Priority
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
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
  • 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,

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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.

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
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

670 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