Export report to Word v3

%REM
    Agent Export to Word
    Created Apr 8, 2014 by Volodymyr Stoyko/TER
    Description: agent collect information from _Personen view
                 and create new reports, based on template, which is
                 stored in document by form Word Report Template (view reportTemplate)
                 In this doc also stored field with count of reports, which is incremented
                 every time, when new reports are generated

                 Report of service consumption for SLA - use this view
%END REM
Option Public
Option Declare

Sub Initialize
    Dim ws As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim template As NotesDocument
    Dim rField1 As NotesRichTextItem
    Dim rField2 As NotesRichTextItem
    Dim embObject As NotesEmbeddedObject
    Dim path, fileName, templateName As String ' path to temp file, template for new files
    Dim countries List As String
    Dim pos, locNumber, locCount, rowCounter, entryCounter, generalCount As Integer

    'collecting data
    Dim collection As NotesView
    Dim CollectionDoc As NotesDocument
    Dim amailServer, aFullName As NotesName
    Dim view As NotesView
    Dim countOfReports As Integer

    ' New document, which will be created somewhere (RecLib)?
    %REM
    Dim attSession As New NotesSession
    Dim attDb As NotesDatabase
    Dim attDoc As NotesDocument
    Set attDb = attSession.CurrentDatabase
    Set attDoc = New NotesDocument ( attDb )
    attDoc.Form = "SaveAttachments"
    Dim rtItem As NotesRichTextItem ' for storing attachments
    ' end of init
    %End Rem

    'Get report tamplate
    Set db = session.CurrentDatabase
    Set view = db.GetView("reportTemplate")
    Set template = view.getfirstdocument
    If template Is Nothing Then
        MessageBox "No configuration document",16
        Exit Sub
    End If
    Set rField1 = template.getfirstitem("template")
    If rField1 Is Nothing Then
        MessageBox "No confiuration document with template",16
        Exit Sub
    End If
    Set embObject = rField1.GetEmbeddedObject("reporttemplate.doc")
    If embObject Is Nothing Then
        MessageBox "Configuration file was now found in configuration document",16
        Exit Sub
    End If

    ' temporary path for storing tamplates
    'set static comment/uncomment
    path = "C:\Users\volodymyr.stoyko\Desktop\reports\"

    'check path
    If IsEmpty(path) Then
        MsgBox "Set correct path in 'path' variable"
        Exit Sub
    End If

    ' Initialize array of locations - those will be used as key to filter collected data
    countries("Marocco") = "AWSM"
    countries("Mexico") = "PUE"
    countries("Tunisia") = "JEN"
    countries("Germany") = "WOB|ING|BLN|CER|CHA|CHD|GRO|SHA|WUX|ZWI|NIN|PAM"
    countries("Poland") = "GOR"
    countries("Ukraine") = "TER|CHE"
    countries("Bolgaria") = "KAR|MEZ"
    countries("Slovakia") = "NIT|BRA"
    countries("Romania") = "CRN"

    ForAll location In countries
        pos = 0 'position in string with few locations

        'word variables
        Dim wordapp As Variant
        Dim worddoc As Variant
        Dim actDoc As Variant
        Dim table As Variant
        Dim selection As Variant

        templateName = Environ("TEMP") + "reporttemplate.doc"
        fileName = path + "SCLN_ServiceConsumptionEvidence_" + ListTag(location) + _
                            Format(Now, "_ddmmyyyy")+".doc"
        Call embObject.Extractfile(templateName)
        Set wordapp = CreateObject("word.application")
        ' one way - extrct document and opent it for editing.
        ' second way - store the dot template and add this template for new docs
        Set worddoc = wordapp.documents.open(templateName)
        wordapp.visible = False

        countOfReports = template.GetItemValue("countOfReports")(0)
        countOfReports = countOfReports + 1

        rowCounter = 1
        generalCount = 0

        Set table = wordapp.Selection.Document.tables(2)

        ' there insert location and count of user in the en

        If InStr(location, "|") <> 0 Then

            While pos < Len(location)

                ' count locations for
                'Start collect data
                'Set collection = db.Getview("_Personen")
                Set collection = db.Getview("Report of service consumption for SLA")

                'If collection.Count  > 0 Then
                'MsgBox CStr(collection.Count) + " |  " + location
                'Беремо перший док
                Set CollectionDoc = collection.GetFirstDocument

                table.Rows.Add ' add location string
                rowCounter = rowCounter + 1
                locNumber = rowCounter
                entryCounter = 0 ' count of docs for location

                Do While Not(CollectionDoc Is Nothing)
                    'if check if it is proper department
                    Set amailServer = New NotesName(CollectionDoc.GetItemValue("MailServer")(0))
                    Set aFullName = New NotesName(CollectionDoc.GetItemValue("FullName")(0))

                    If aFullName.Organization = _
                        Left(Right(location,Len(location)-pos), 3)  Then
                            With table
                                .Rows.Add   ' add new row
                                rowCounter = rowCounter + 1
                                entryCounter = entryCounter + 1
                                ' NotesName abbreviated or this shit left-right-Instr
                                .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                                .Cell(rowcounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                                .Cell(rowCounter, 3).Range.InsertAfter(CollectionDoc.GetItemValue("InternetAddress")(0))
                                .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                                .Cell(rowCounter, 5).Range.InsertAfter(CollectionDoc.GetItemValue("MailFile")(0))

                            End With
                        End If

                    Set CollectionDoc  = collection.GetNextDocument(CollectionDoc)
                Loop
                'End If

                ' check if no records - delete created organization row
                ' else - add info

                If entryCounter = 0 Then
                    'MsgBox Left(Right(location,Len(location)-pos), 3)
                    table.Rows(locNumber).Delete
                    rowCounter = rowCounter -1
                Else
                    table.Rows(locNumber).Cells.Merge
                    table.Cell(locNumber,1).Range.InsertAfter("Location: " + _
                    Left(Right(location,Len(location)-pos), 3) + _
                    ". Quantity of users: " + CStr(entryCounter))
                    table.Rows(locNumber).Range.Font.Bold = True
                    generalCount = generalCount + entryCounter
                End If
                'move to next organization
                pos = pos + 4
            Wend

        Else

            'Set collection = db.GetView("_Personen")
            Set collection = db.Getview("Report of service consumption for SLA")

            Set CollectionDoc  = collection.GetFirstDocument

            table.Rows.Add ' add location string
            locNumber = table.Rows.Count
            entryCounter = 0 ' count of docs for location
            rowCounter = rowCounter + 1
            'поки не закінчилися дані
            Do While Not(CollectionDoc Is Nothing)

                'if check if it is proper department
                Set amailServer = New NotesName(CollectionDoc.GetItemValue("MailServer")(0))
                Set aFullName = New NotesName(CollectionDoc.GetItemValue("FullName")(0))

                If aFullName.Organization = location Then
                    With table
                        .Rows.Add   ' add new row
                        rowCounter = rowCounter + 1
                        entryCounter = entryCounter + 1
                        ' NotesName abbreviated or this shit left-right-Instr
                        .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                        .Cell(rowcounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                        .Cell(rowCounter, 3).Range.InsertAfter(CollectionDoc.GetItemValue("InternetAddress")(0))
                        .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                        .Cell(rowCounter, 5).Range.InsertAfter(CollectionDoc.GetItemValue("MailFile")(0))

                    End With
                End If

                Set CollectionDoc=collection.GetNextDocument(CollectionDoc)
            Loop

        generalCount = entryCounter

        table.Rows(locNumber).Cells.Merge
        table.Cell(locNumber,1).Range.InsertAfter("Location: " + location + _
        ". Quantity of users: " + CStr(generalCount))
        table.Rows(locNumber).Range.Font.Bold = True

        End If

        ' summary
        Set table = wordapp.Selection.Document.tables(1)
        table.Cell(2,1).Range.InsertAfter(" " + CStr(countOfReports))
        table.Cell(2, 2).Range.InsertAfter(" " + ListTag(location))
        table.Cell(2, 3).Range.InsertAfter(" " + Format(Now, "mmmm yyyy"))
        table.Cell(3, 1).Range.InsertAfter(" " + CStr(generalCount))


        Call worddoc.Saveas(fileName)
        worddoc.Close
        Set wordDoc = Nothing
        wordapp.Quit
        Set wordapp = Nothing


        'add files to attachment-doc and remove from disc
        ' uncomment code with attDoc init first, then code below to upload file to new document
        ' in some external DB (RecLib for example)
        %Rem
        Set rtitem =  New NotesRichTextItem( attDoc, "saveAttachment" )
        Call rtitem.EmbedObject ( EMBED_ATTACHMENT, "", fileName)
        Kill fileName ' deleted file after adding
        %End Rem
    End ForAll

    'add new counter to template doc
    template.countOfReports = countOfReports
    Call template.Save(True, False)

    ' Save document with attachments (uncomment if you upload reports to document in notes DB)
    'Call attDoc.save(True, False)
End Sub