A document carries 50 fields out of 10 fields are like,ts_txtNum_1,ts_txtNum_2…ts_Num_10
and corresponding fields,
ts_txtDet_1,ts_txtDet_2…ts_txtDet_10
I want to export this data from view to Excel with an Agent
My columns are,
1)Serial No,3)Name, 3)Number,4)Deatails
My problem is how to export this data for one document
in following format
SerialNo Name Number Details
1 xyz 1 abc
1 xyz 2 kjk
1 xyz 3 ghh
" " " "
1 xyz 10 hhg
Number(ts_txtNum_1…ts_txtNum_10)
detail(ts_txtDet_1…ts_txtDet_10)
When it will go to next document then it has to change
serial no & Name and corresponding data of number & Details
what should be my looping code
to increment & export Number & Detail fields
along with same number & Name for a document
Any advice appreciated
Subject: EXPORT TO EXCEL: How to loop for the alike fields?
Do You mean something like this? Having trouble to understand Your question, but I’m guessing here…
Sub Initialize
Dim sess As New NotesSession
Dim db As NotesDatabase
Dim vw As NotesView
Dim doc As NotesDocument
Dim xlApp As Variant
Dim itm As NotesItem
Dim i As Integer
Set db = sess.CurrentDatabase
Set vw = db.GetView("Default")
Set doc = vw.GetFirstDocument
Set xlApp = CreateObject("Excel.Application")
Call xlApp.Range("A1").Activate
Do Until (doc Is Nothing)
With xlApp
.ActiveCell.Value = "Serial No"
.ActiveCell.Offset(0,1).Activate
.ActiveCell.Value = "Name"
.ActiveCell.Offset(0,1).Activate
.ActiveCell.Value = "Number"
.ActiveCell.Offset(0,1).Activate
.ActiveCell.Value = "Details"
.ActiveCell.Offset(1,-3).Activate
For i = 1 To 10 Step 1
.ActiveCell.Value = doc.SerialNo(0)
.ActiveCell.Offset(0,1).Activate
.ActiveCell.Value = doc.Name(0)
.ActiveCell.Offset(0,1).Activate
Set itm = doc.GetFirstItem("ts_txtNum_" & Cstr(i))
If Not (itm Is Nothing) Then
.ActiveCell.Value = itm.Text
End If
.ActiveCell.Offset(0,1).Activate
Set itm = doc.GetFirstItem("ts_txtDet_" & Cstr(i))
If Not (itm Is Nothing) Then
.ActiveCell.Value = itm.Text
End If
.ActiveCell.Offset(0,1).Activate
' Next line!
.ActiveCell.Offset(1,-3).Activate
Next
End With
Set doc = vw.GetNextDocument(doc)
Loop
Call xlApp.ActiveWorkbook.SaveAs(“C:\tmp\MyFile.XLS”)
Call xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
End Sub