Manage Learn to apply best practices and optimize your operations.

Export Lotus Notes data to Microsoft Excel using the ADODB.Recordset object

Learn how to export a large amount of Lotus Notes data to a Microsoft Excel spreadsheet using LotusScript, the ADODB.Recordset object and SQL.

If you need to export or update a large amount of Lotus Notes data to a Microsoft Excel spreadsheet using LotusScript, you can use ADODB.Recordset and SQL.

You can even update the Microsoft Excel data using the same process; you just need to change the SQL data.

For example, once you have added the data you can retrieve it by using the SQL "Select * from Empdata, where EmployeeID = 'Emp#10'". Also, you should make sure you update the value using oRs(1).Value = "New Name."

  1. Create a Microsoft Excel file (for example, on "D:TempExcelFile.xls).


  2. Write some labels on row 1 as:
    EmployeeID        EmployeeName

    "EmployeeID" can be on cell A1 and "EmployeeName" on Cell B1.


  3. Now use menu "Insert>>Name>>Define."


  4. Write "EmpData" in the "Names in Workbook" field.


  5. Select Range "=Sheet1!$A$1:$B$1" in the "Refers To" field and click OK.


  6. Save the Microsoft Excel spreadsheet.


  7. Write the following LotusScript code on some form button:
    Dim ORs As Variant
    ReportPath = "D:TempExcelFile.xls"
    Set oRs = CreateObject("ADODB.Recordset")                  
    oRs.Open "Select * from EmpData",
    {Provider = "Microsoft.Jet.OLEDB.4.0";Data Source=}
     & ReportPath & {; Extended 
    Properties="Excel 8.0;HDR=NO:" },1,3 
    For I = 1 To 200
    oRs(0).Value = "Emp#" & Trim$(Cstr(I))
    oRs(1).Value = "Employee Name"  & Trim$(Cstr(I))

Do you have comments on this tip? Let us know.

Related information from


Dig Deeper on LotusScript

  • Favorite iSeries cheat sheets

    Here you'll find a collection of valuable cheat sheets gathered from across the iSeries/ community. These cheat ...

  • HTML cheat sheet

    This is a really cool cheat sheet if you're looking to learn more about HTML. You'll find just about everything you every wanted ...

  • Carol Woodbury: Security

    Carol Woodbury