This is an incredibly easy way to link fields in your Notes document to cells in an Excel spreadsheet. This is great for calculating IRR and Net Present Value, or anytime you need a spreadsheet to number crunch something or make a chart for you. This example shows how to link a couple of fields to cells. Once you have this down, the sky's the limit.
- Create fields on your Notes document that you want to link to the spreadsheet.
- In Designer, create fields on your Notes Form that you want to link with Excel (i.e., Data1, Data2). I have always used text fields, but you can try number and name fields and see if they work.
- Embed the spreadsheet you want to link to on the Notes Form in Designer.
- Embed the Excel spreadsheet you want to link to on your Notes document using the Create -> Object command. If you already have an existing spreadsheet you want to use, select the "Object from a file" option and click the Browse button to find the file on your computer. If you just want to start with a blank spreadsheet, use the "Object" option and select "New Microsoft Excel Spreadsheet" from the list.
- Create titles for your data cells (optional but recommended). In Designer, double click the embedded worksheet to open it. If you do not already have any titles for your fields, create titles to the left of each cell you want to link to (i.e., Data 1, and Data 2). You might want to make them slightly different than the field names on the Notes form so you don't get them confused.
- Define the cells in Excel that you want to link to. In Excel, highlight the first cell you want to link to and select Insert -> Name -> Define. Excel will open a Define Name window and automatically default to using the name of the field to the left as the Name.
Note: Excel replaces the space with an "_" in the Name. If you decided to skip Step 5 you will need to enter a name manually. It will also automatically define the Refers to: at the bottom. Just click OK. Then repeat this step for every cell you want to link to.
- Create the links between the Excel cells and the Notes fields in Excel, go to File -> Properties and select the Custom Tab. In the Name area, enter the Name of the Notes field (i.e., Data1), then check the "Link to content box." Select the Source from the list of Excel cells you defined in Step 6 (i.e., Data_1), then click the Add button. Repeat this for every field and cell you want to link. Then click OK.
- Test the links. In Designer, save the form and preview it in the Notes Client. Enter some data in your data fields on the Notes form, then double click the Worksheet Icon. When Excel opens, the data in the linked cells should match the data in the Notes fields; if your cells are empty or all zeros, check the troubleshooting section below.
Note: If you change the data in the linked fields on the Notes document, you will need to open and close the spreadsheet to update it's data as well.
I've done everything correct, but when I open my spreadsheet everything is blank.
You need to make sure that Windows can find your Notes.ini file. Either make sure the location of you notes.ini file is in your path statement (in your Environment Settings), or MOVE your Notes.ini file into a directory that is in your path. (i.e., C:Windows). Make sure you are using the correct Notes.ini file. Do a search on your C drive and make sure you don't have more than one Notes.ini file. If you do, check the modified dates of the files and keep the one that was modified most recently. Rename the other one to something like NotesOld.ini. Make sure the good Notes.ini is in your Windows path.
This user has rediscovered a feature that was originally implemented in Notes R3 called Notes FX, or Notes Field Exchange. Lotus even had a set of applets, including a spreadsheet, project management, charts and others that you could embed into your Notes application.
It was a cool idea that never caught on and most people forgot about it. In fact, I was trying to use this feature a few years ago (around 2001) and I called Notes Technical Support; the technician I talked to didn't know what I was talking about. I ended up writing COM code to generate a spreadsheet on the fly instead of using the Notes FX feature.
Good tip! This is what I managed to do one week ago. At first, I was not able to see the data exchange between Notes and Excel. I have gone through the Help section and other things, and this is what found that I thought could be helpful.
- In R5 or greater, Notes.ini is installed in the data directory. So there is no need to put the Notes.ini file in the Windows directory. Leave it as is in the data directory and that should work. But, make sure that there is only one Notes.ini in the data directory and it is pointing to one single server.
- Whenever you do modifications to the original Excel file, you need to delete the embedded object and reload it again.
- Check the "Update document from document" option for the object property.
This technology is nothing but Notes FX.
Thanks, Paul Bleeker for your elaborate description. Your tip made it easy for me to understand.
Do you have comments on this tip? Let us know.
This tip was submitted to the SearchDomino.com tip exchange by member Paul Bleeker. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.
This was first published in September 2004