如何导出指定月份的excel

Question

Sub Initialize
    'Copyright Botstation (www.botstation.com)
    Dim session As New NotesSession
    Dim wks As New NotesUIWorkspace
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim uiView As NotesUIView
    Dim doc As NotesDocument
    Dim column As NotesViewColumn

    Dim row As Long,colcounter As Long,arrcnt As Long,arrcounter As Long, x As Long
    Dim filename As String, currentvalue As String
    Dim rowsatonce As Integer,cn As Integer
    Dim xlApp As Variant, xlsheet As Variant,xlwb As Variant, xlrange As Variant, tempval As Variant
    Dim DataArray
    Dim VColumns List As String

    ReDim DataArray(0, 80) As String
    '80 columns is our expected max number of columns in the view. It's dynamically recomputed below to actual (lower) number. Change if the number of columns is larger.

    Set db=session.CurrentDatabase
    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True 'Excel program is visible (to avoid errors and see what is happening)

    Set xlwb=xlApp.Workbooks.Add
    Set xlsheet =xlwb.Worksheets(1)

    Set uiView = wks.CurrentView
    Set view = db.GetView( uiView.ViewName ) ' get the view currently open in UI
    arrcnt=0
    row=1
    colcounter=0
    rowsatonce=20
    ForAll c In view.Columns
        If c.isIcon<>True Then ' do not include icon columns
            If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are used for counting docs (Total)
                colcounter=colcounter+1
                DataArray(row-1, colcounter-1) =c.Title
                VColumns(CStr(cn))=CStr(cn)
            End If
        End If
        cn=cn+1
    End ForAll
    ReDim Preserve DataArray(0, colcounter-1) As String
    xlsheet.Range("A1").Resize(1, colcounter).Value = DataArray ' set column names
    ReDim DataArray(rowsatonce-1, colcounter-1) As String
    row=2
    x=0
    Set doc = view.GetFirstDocument
    While Not ( doc Is Nothing )
        ForAll col In VColumns
            currentvalue=""
            tempval= doc.ColumnValues(Val(col))
            If IsArray(tempval) Then
                ForAll v In tempval
                    If currentvalue="" Then
                        currentvalue=v
                    Else
                        currentvalue=currentvalue+","+v
                    End If
                End ForAll
            Else
                currentvalue=tempval
            End If
            x=x+1
            DataArray(arrcounter, x-1) =currentvalue
        End ForAll
        x=0
        row=row+1
        arrcounter=arrcounter+1
        If arrcounter/rowsatonce=arrcounter\rowsatonce And arrcounter<>0 Then
            xlsheet.Range("A"+Cstr(arrcnt*rowsatonce+2)).Resize(rowsatonce, colcounter).Value = DataArray
            arrcnt=arrcnt+1
            arrcounter=0
            ReDim DataArray(rowsatonce-1, colcounter-1) As String
        End If
        Set doc = view.GetNextDocument (doc)
    Wend

    If arrcounter/rowsatonce<>arrcounter\rowsatonce And arrcounter>0 Then
        ' Redim Preserve DataArray(arrcounter, colcounter-1) As String
        xlsheet.Range("A"+Cstr(arrcnt*rowsatonce+2)).Resize(arrcounter, colcounter).Value = DataArray
    End If
    MsgBox "Done"

End Sub

Answer 1

获得要包含的月份后，您可以在此行后添加一个条件：

While Not ( doc Is Nothing )

将月份（可能还有年份）与文档上的（日期）项目进行比较。您可能需要 NotesDateTime 类来执行此操作。

要过滤正确的月份，您可以执行以下操作：（假设您还需要年份）

If year(date1) * 100 + month(date1) = year(date2) * 100 + month(date3)