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


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.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

HemanthaKumarConnect With a Mentor Commented:
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.

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

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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

PaulCutcliffeAuthor Commented:
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,

PaulCutcliffeAuthor Commented:
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.
PaulCutcliffeAuthor Commented:
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...
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.

PaulCutcliffeAuthor Commented:
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'?

PaulCutcliffeAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.