how to remove empty line in between

Question

how to remove empty line in between two firm.

 {.....some code for calling excel template and write   

    Dim lsvw As NotesView, lsdoc As NotesDocument, lsdc As NotesDocumentCollection
    Dim savw As NotesView, sadoc As NotesDocument, sadc As NotesDocumentCollection
    Dim firmvw As NotesView, firmdoc As NotesDocument
    Dim firmve As NotesViewEntry
    Dim firmvc As NotesViewEntryCollection
    Dim tmpve As NotesViewEntry '   temporary use

    Dim firmArr         '   array that contain Company ID
    Dim firmid          '   firm id to store all firm


    Set firmvw = db.Getview("Company Information by Co_ID")
    Set lsvw = db.Getview("(LS sort by Co_ID)")
    Set savw = db.Getview("SA sort by LS Num")


    Set firmvc = firmvw.Allentries      '   get all entries
    If firmvc.Count = 0 Then            '   if all view entry collection is empty 
        Print "No Company information!"
        Exit Sub 
    End If

    Set firmve = firmvc.Getfirstentry()

    firmArr = ""
    firmid = ""
    Do While Not firmVe Is Nothing
        Set firmdoc =firmve.Document
        firmid = firmid + firmdoc.Co_ID(0) + ";"    '   put all co Id store inside firmID
        Set tmpve = firmvc.Getnextentry(firmVe)
        Set firmVe = tmpve
    Loop

    firmArr = FullTrim(ArrayUnique(Split(firmid, ";"))) '   split all firm with ";" so become array 

    '   ForAll refvar In container
    '       [statement]
    '   End ForAll

    row = 2
    ForAll firm In firmArr
        Dim codoc As NotesDocument
        Set codoc = firmvw.Getdocumentbykey(firm,True)
        If Not codoc Is Nothing Then
            xlsht.Cells(row, 1) = Codoc.Co_Name(0)
        End If
        Set lsdc = lsvw.GetAllDocumentsByKey(firm,True)
        Set lsdoc = lsdc.GetFirstDocument

        Do While Not lsdoc Is Nothing

            xlsht.Cells(row, 2) = lsdoc.Name(0)

            Set sadc = savw.GetAllDocumentsByKey(lsdoc.Reg_Num_LS(0),True)
            Set sadoc = sadc.GetFirstDocument

            Do While Not sadoc Is Nothing

                xlsht.Cells(row, 3) = sadoc.Name(0)
                xlsht.Cells(row, 4) = sadoc.NRIC(0)
                xlsht.Cells(row, 5) = sadoc.Date_Apprv_Cr(0)
                row = row +1
                Set sadoc = sadc.GetNextDocument(sadoc) 

            Loop

            row = row +1
            Set lsdoc = lsdc.GetNextDocument(lsdoc)
        Loop

        row = row + 1   '   write every row during pass one company
    End ForAll  


Call xlWbk.Save
Call xlWbk.Close
Set xlWbk = Nothing
Call xl.Quit
Set xl = Nothing

Set rtitem = New NotesRichTextItem(doc, "Attachment")
Call rtitem.Embedobject(EMBED_ATTACHMENT, "", template)
If template <> "" And Dir$(template, 0) <> "" Then Kill template


Call doc.Save(True, False)

Code is doing:

1. Firstly add all the firm store inside an array.
2. After that loop through all the array.
3. Each time use firm to look for a document go search for a land surveyor then write it into the excel.
4. After that, use land surveyor to look for a surveying assistant that follow him and also write into excel.

problem : every time loop pass a company will add a new line, but it seem give double line in between, any idea which part of my code is wrong. Thanks!

Answer 1

You have three different lines that say row = row + 1, within three nested loops. If you trace the logic for your first case, you encounter one of these for each of the three surveying assistants (sadoc), one for the land surveyor (lsdoc), and then one for the firm. That's five times that you execute row = row + 1 but you only generated three lines of data because the lsdoc information and firm information are on the same line as the first sadoc information.

If there is always at least one sadoc for each lsdoc, and always only one land surveyor for each firm, then the answer is easy: just get rid of the two extra row = row + 1 lines. Unfortunately, I see that you have a case where there is more than one lsdoc for one firm, and in that same case there is no sadoc for the second lsdoc for the firm so it's not going to be that simple.

You're going to have to keep track and only execute row = row + 1 when it is really necessary. I.e., change this

row = row +1
Set sadoc = sadc.GetNextDocument(sadoc) 

To this

Set sadoc = sadc.GetNextDocument(sadoc) 
If not sadoc is Nothing then
   row = row +1
End If

And do the same trick for lsdoc, too.