EXPORT TO EXCEL: How to loop for the alike fields?

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