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.Addnew
    oRs(0).Value = "Emp#" & Trim$(Cstr(I))
    oRs(1).Value = "Employee Name"  & Trim$(Cstr(I))
    Next
    oRs.Update
    oRs.Close
    

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

Related information from SearchDomino.com:

 

This was first published in April 2007

Dig deeper on LotusScript

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close