Menu

« New Import Export Library release - now with much better format-control on import | Main| New Import Export Library release - now support import/export of Comma Separated Files (CSV) »

How to create Notes Import Export Library Toolbar with buttons for export to Excel

Category Import Export Library

You know how to launch the Import Export Library from the File Import and Export menus. How about creating a toolbar where you can instantly export all or just the selected documents to Excel? Learn how to create a nice toolbar like this:

image

First, download the icons you’ll need later from this page.

Begin by selecting the File -> Preferences. In the upcoming dialog box, choose the Toolbar section. You will see something like the following screen shot;

image
Click on the New Toolbar button. You'll see an empty new line there, like so;

image

Enter something you want to call your new toolbar, such as "Import Export Library". Press enter when finished.

Now choose the Customize section (marked by 1) ...

image

... and then ensure that your newly created toolbar is selected in the Toolbar to Customize (marked by 2 above).

Now click the New-button and select Text  menu line, as shown below;

image
A dialog box pops up allowing you to enter text, as shown below;

image
Enter something that you want to see in your toolbar, such as "Export view to Excel".

Then it is time to add the first button. This button will be the button that just launch Notes Import Export Library in export-mode, with the dialog box, so you can control everything yourself. This is the same as choosing one of the Excel export-menus in the File -> Import dialog box.

First press the New-button again, and choose the Button menu line afterwards, as shown here;

SNAGHTMLaad97c

A new, empty, "Edit Toolbar Button" dialog box pops up, as seen below:

image
Note that we have some fields we can fill in. The first Button caption text is the text as we will see it it in the preferences dialog box etc. Since this is a buttonand not a text , we will first and foremost see only the icon in the toolbar. The Popup help text is the text that will be shown when you hover the mouse over the toolbar button later. The Formulais the actual @formula to run when the button is pressed. Finally you can change the icon.

Lets start with the text fields Button caption text , Popup help text  and Formula (by the way, to make it much simpler for you to both read these instructions -and- to edit the dialog box at the same time, I suggest that you copy the following sections to a text editor som you can easily copy and paste between the editor and Notes). Another tips with the formula-text is that you can edit in the a separate (and resizeable) window by clicking on the Formula Window-button.

#### Start copy to clipboard ####

Button Caption Text:
Export current view *with* dialog box ...

Popup help text:
Export all the documents in the current view to Excel and show the dialog box

Formula:

REM {Export the current view to Excel *with* the dialog box. Since strExcelFileName is blank, it will also ask for a file name};

strServer := @Subset(@DbName; 1);
strDatabase := @Subset(@DbName; -1);
tmpViewList := @ViewTitle;
strView := @If(@Elements(tmpViewList) > 1; @Subset(tmpViewList; -1); tmpViewList);
strExcelFileName := "";
strExportNotesViewFormat := "1";
strGroupExcelRows := "0";
strLaunchDirectly := "0";

REM {Check if we have a frameset issue, meaning that the button doesn't know which view it came from};
@If(strView = ""; @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Can't determine which view you came from. Please export via File -> Export instead");@Return(""));"");

REM {Launch IEL with the parameters above};
rc := @DbCommand("VCIEL":"NoCache";"ExportViewToExcel"; "Prompt"; strServer ; strDatabase ; strView; strExcelFileName; strExportNotesViewFormat; strGroupExcelRows; strLaunchDirectly;"");

REM {Check if we had an error, such as IEL not being installed};
@If(@IsError(rc); @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Looks like Notes Import Export Library isn't installed. Please contact IT-support"); @Return(""));"");
@If(rc != 0; @Prompt([Ok];"Export view to Excel"; "Error code=" + @Text(rc));"")


#### End copy to clipboard ####

To change the icons to something better than a blue ball, click the Change icon-button.  You will see the dialog box below;

image
Unless you find something you like in the list of image resources above (not much likely!!), click on the New-button. This will open a standard File-selector dialog box. Select the first "Notes Import Export Library - Export.gif" file. Click OK when done.

Your "Edit Toolbar Button"-dialog box should then look something like this:

image
Click OK to save.

Ok, to make things look nice I add a small separator now. This will make a small space between the first icon and the two next icons:

image

... and ...

image


Now you are ready to add the second button. Repeat the procedure for the first button. The icon you want to use now is the "Notes Import Export Library - Export all to Excel.gif".  Below you see the second button data;

#### Start copy to clipboard ####

Button Caption Text:
Export all documents in view to Excel ...

Popup Help Text:
Export all the documents in the current view to Excel instantly!
       
Formula:

REM {Export all the documents in  the current view to Excel *without* the dialog box. Note how I specifies a filename in the Exported Excel-folder on the desktop};

strServer := @Subset(@DbName; 1);
strDatabase := @Subset(@DbName; -1);
tmpViewList := @ViewTitle;
strView := @If(@Elements(tmpViewList) > 1; @Subset(tmpViewList; -1); tmpViewList);

strExcelFileName := "$(DESKTOPFOLDER)\\Exported Excel Reports\\"+ @ReplaceSubstring(@DbTitle + "_" + strView + "_" + @Text(@Now; "D0T0S2"); ".":":";"":"") ;
strExportNotesViewFormat := "1";
strGroupExcelRows := "0";
strLaunchDirectly := "1";

REM {Check if we have a frameset issue, meaning that the button doesn't know which view it came from};
@If(strView = ""; @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Can't determine which view you came from. Please export via File -> Export instead"); @Return(""));"");

REM {Launch IEL with the parameters above};
rc := @DbCommand("VCIEL":"NoCache";"ExportViewToExcel"; "All"; strServer ; strDatabase ; strView; strExcelFileName; strExportNotesViewFormat; strGroupExcelRows; strLaunchDirectly;"");

REM {Check if we had an error, such as IEL not being installed};
@If(@IsError(rc); @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Looks like Notes Import Export Library isn't installed. Please contact IT-support"); @Return(""));"");
@If(rc != 0; @Prompt([Ok];"Export view to Excel"; "Error code=" + @Text(rc));"");


#### End copy to clipboard ####

Finally you are ready for the last button. Now you know the drill too! The icon you want to use now is the "Notes Import Export Library - Export all to Excel.gif".  Below you see the thrid button data;

#### Start copy to clipboard ####

Button Caption Text:
Export selected documents in view to Excel ...

Popup Help Text:
Export the selected documents in the current view to Excel instantly!
       
Formula:

REM {Export all the selected documents in  the current view to Excel *without* the dialog box. Note how I specifies a filename in the Exported Excel-folder on the desktop};

strServer := @Subset(@DbName; 1);
strDatabase := @Subset(@DbName; -1);
tmpViewList := @ViewTitle;
strView := @If(@Elements(tmpViewList) > 1; @Subset(tmpViewList; -1); tmpViewList);

strExcelFileName := "$(DESKTOPFOLDER)\\Exported Excel Reports\\"+ @ReplaceSubstring(@DbTitle + "_" + strView + "_" + @Text(@Now; "D0T0S2"); ".":":";"":"") ;
strExportNotesViewFormat := "1";
strGroupExcelRows := "0";
strLaunchDirectly := "1";

REM {Check if we have a frameset issue, meaning that the button doesn't know which view it came from};
@If(strView = ""; @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Can't determine which view you came from. Please export via File -> Export instead"); @Return(""));"");

REM {Launch IEL with the parameters above};
rc := @DbCommand("VCIEL":"NoCache";"ExportViewToExcel"; "Selected"; strServer ; strDatabase ; strView; strExcelFileName; strExportNotesViewFormat; strGroupExcelRows; strLaunchDirectly;"");

REM {Check if we had an error, such as IEL not being installed};
@If(@IsError(rc); @Do(@Prompt([Ok]; "Export View to Excel"; "Ooops!! Looks like Notes Import Export Library isn't installed. Please contact IT-support"); @Return(""));"");
@If(rc != 0; @Prompt([Ok];"Export view to Excel"; "Error code=" + @Text(rc));"");


#### End copy to clipboard ####

That's it! Now you have your own toolbar with some powerful Import Export buttons ready to use in any ordinary Notes-view. Below you see the toolbar you just have created;

image

A small walkthrough on what the Formula-code do. Below you see the formula window for button number 2 (Export all documents in view...)

image

At (1) I determine which server, database and view you are currently working in.

At (2) I set the output folder and file name of the Excel file generated by IEL. Note that IEL knows a whole lot about system variables, and the variable @(DESKTOPFOLDER) is a variable pointing to your Desktop. the variable strExcelFileName will contain the full path to your folder Exported Excel Reports located on your desktop.

image

The @ReplaceSubstring-formula ensures that the file name is valid (IEL does also check this behind the scenes), while the rest of the code construct an Excel file name consisting of the database title, the view date and a time stamp. The result looks like this;

image
At (3) I set some IEL-related variables. These are:
strExportNotesViewFormat = "1" ; - IEL will do it's best at creating a similar appearance in the Excel spreadsheet as found in your Notes view
strGroupExcelRows = "0"; IEL won't group any categories for you.
strLaunchDirectly = "1"; IEL will export the view and launch the Excel spread sheet instantly.

At (4) I check if I find the environment to be a view. If only a frameset, or document, or something that IEL can't figure out to be a real Notes view, it stops now.

At (5) we can IEL as a Notes Database Driver command with all the parameters specified above. Note the "All"-parameter. It instructs IEL to export all the documents in the view this time. Use "Selected" to export only the selected documents.

At (6) I attempt to pick up any error and display it to the user.

Hopefully this gives your IEL-use a nice boost :-)