 |
|
| Domino Tips: |
|
|
|
LOTUSSCRIPT
Export Lotus Notes data to Microsoft Excel using the ADODB.Recordset object
Humayun Tareen
04.10.2007
Rating: -3.32- (out of 5)
|
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."
- Create a Microsoft Excel file (for example, on "D:TempExcelFile.xls).
- Write some labels on row 1 as:
EmployeeID EmployeeName
"EmployeeID" can be on cell A1
and "EmployeeName" on Cell B1.
- Now use menu "Insert>>Name>>Define."
- Write "EmpData" in the "Names in Workbook" field.
- Select Range "=Sheet1!$A$1:$B$1" in the "Refers To" field and click OK.
- Save the Microsoft Excel spreadsheet.
- 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:
Tip: Export a Lotus Notes view to Microsoft Excel without coding
Tutorial: How to import data into Lotus Notes -- without programming
Learning Center: LotusScript and Excel
FAQ: LotusScript advice
Learning Guide: LotusScript
Reference Center: LotusScript tips and resources
This tip was submitted to the SearchDomino.com tip library by member Humayun Tareen. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchDomino.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
