I've posted some code before for exporting from Notes to Excel. Now I'm posting a more complete routine, a bit more robust, with many added comments. I get asked on a semi-regular basis for code - or explanation of the "how" - for getting Notes and Excel to talk to each other. Well, given that one is running on top of Windows, Notes and Excel are just 2 of many applications that can communicate with each other using COM and OLE. So here is the export2Excel routine. This sample code is written as an agent that would run from within your personal Name and Address Book, and will export some of the fields from the Contact documents. Now I won't have to hunt around for an example the next time I'm asked -- or the next time I need to code one of these routines :-) (also be sure to check out the Notes to Excel Reporter free download at Steve Castledine's ProjectDX site).

I've tried to document this code fairly well (just remember, this is a freebie, so no complaints allowed!). But what may not be apparent is how to come up with the code to do the formatting of the Excel file after the data is exported. The answer is that I simply tweaked some code that Excel created for me. Here's how:

The first step in using LotusScript from Notes to automate an Excel function, is to let Excel create some of the code for you. From the menu bar within Excel, click Tools > Macro > Record New Macro. You'll need to provide a name for the macro. Then go ahead and do whatever formatting or other tasks you want to automate, just like you normally would. When done, click the small blue icon to stop the recording (the icon will be displayed in a small window on the Excel sheet when you start recording). Now it's time to have a look at the code that Excel generated. Click Tools > Macro > Macros. Select the macro that you just recorded, and click Edit.

What appears in a new window is the VBA code (Visual Basic code) that Excel created for you while recording the macro. Very cool. Much of this code can be pasted directly into your LotusScript code, but you will definitely need to provide values for any Excel constants that appear in the code. So how do you find those values? Well one easy way is to create a small macro in Excel and let the macro tell you the needed value.

From the Excel sheet, click Tools > Macro > Macros, click the Create button to create a new macro, type in a name for your macro, and click Edit. Now, let's say you want to find the value that you need to provide to LotusScript for the Excel constant named 'xlPaperLetter'. In the new Sub module that Excel created for you, type in this single line of code:
  MsgBox "The value of xlPaperLetter is <" & xlPaperLetter & ">"
Now go back to the Excel window that has the worksheet open, and click Tools > Macro > Macros, select the new macro you just created, and click Run. A message box will pop up displaying this message:
  The value of xlPaperLetter is <1>
Well, that tells you that if you want to use the 'xlPaperLetter' Excel constant from within your LotusScript code, you will need to create an Integer variable (or constant) with a value of numeric 1. For example, you could add this line to the Declarations of your LotusScript routine:
  Const xlPaperLetter = 1

If you follow this procedure to discover the value of other Excel constants, you will soon realize that there are many constants with the same value. Now if you really want to save some time, you can download a Notes database listing the MS-Office constants (thanks to John Coolidge and Duffbert.)