Databases
StackOverflow2013
Postsdbo
POHow to export to excel with specified month
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to export to excel with specified month
    text
    copied!<pre><code>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&lt;&gt;True Then ' do not include icon columns If c.Formula&lt;&gt;"""1""" And c.Formula&lt;&gt;"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&lt;&gt;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&lt;&gt;arrcounter\rowsatonce And arrcounter&gt;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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload