I’m very new to using lotusscript. And I was asked by my boss to export views from a database inside excel. That is by selecting checkmarked documents and using an action button, transfers the data from the view into excel. After searching around the internet and working on the codes itself, this is the most I can do. But I still can’t get the codes working right. It keeps giving the “variant does not contain container” prompt. Can anyone help me with this?
Here’s the code that I’ve been working on
Sub Click(Source As Button)
Dim workspace As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim collection As NotesDocumentCollection
Dim doc As NotesDocument
Dim maxFiles As Integer, i As Integer
Dim scope As String
Dim rows As Integer, cols As Integer, maxcols As Integer
Dim excel As Variant
Dim excelSheet As Variant, colvals As Variant
Dim K As Integer
Dim vwnav As NotesViewNavigator
Dim entry As NotesViewEntry
Dim c As NotesViewColumn
Dim Filename As String
Dim viewCol As NotesViewEntryCollection
Filename = Inputbox(" Enter filename :")
rows=2
cols =1
Set db = session.CurrentDatabase
Set view = db.GetView("Main Form")
Set collection = db.UnprocessedDocuments
maxFiles = collection.Count
Msgbox("Number of generated column = " &maxFiles)
Set excel=createobject("excel.application")
excel.StatusBar = "Creating worksheet...."
excel.Workbooks.Add
excel.visible=True
excel.displayAlerts=False
excel.ReferenceStyle = 2
Set excelSheet = excel.Workbooks(1).Worksheets(1)
excelSheet.Cells(rows, cols).value = "IBM CONFIDENTIAL"
excelSheet.Cells (rows + 2, cols).value = "Ref : "
excelSheet.Cells(rows+4, cols).value= "To : Krishnan Kannan"
excelSheet.Cells(rows+6, cols).value = "Date : " + mon
excelSheet.Cells(rows+8,cols).value = "Subject : " + Filename
excel.StatusBar = "Creating..."
rows=13
maxcols = 5
For K=1 To maxcols
Set c=view.columns(K-1)
excelSheet.Cells(rows,cols).Value = c.title
cols = cols + 1
Next K
Call collection.PutAllInFolder("temp")
Set view = db.GetView("temp")
Set viewCol = view.AllEntries
For cols = 1 To viewCol.Count
Set entry = viewCol.GetNthEntry(cols)
colvals=entry.ColumnValues(cols-1)
Select Case scope
Case "STRING"
excelsheet.Cells(rows,cols).Value ="'" + colvals
Case Else
excelsheet.Cells(rows,cols).Value = colvals
End Select
Next cols
Call collection.RemoveAllFromFolder( "temp" )
Print "Done processing " & viewCol.count & " documents."
Something like You have problem with excell controll initialization (or any of xcell objects). Try to use a debugger or trap the bug and print Erl - otherwise You know only that bug is “somewhere” in the script.
I’ve debugged the program as you have advised, this line seems to be the one causing the problem.
colvals=entry.ColumnValues(cols-1)
I’ve tried changing the codes with different arguments etc, but it still doesn’t work. It still gives me the “variant does not contain container problem”.
Did you manage to display the informations selected from your view or it only displayed as far as the column header only? Each time I click on the button, it only go as far as creating the excel document and displaying the column header. But none of the documents I click on is displayed.
And thanks for the help. Hope I’m not asking too much
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim fmlaitem As notesitem
Dim myobject As Variant
Dim mysheet As Variant
Dim uiview As NotesUIView
Dim ws As New NotesUIWorkspace
Dim collection As NotesDocumentCollection
Dim column As NotesViewColumn
On Error Goto generalError
Set db=session.currentDatabase
Print "Please Be Patient as the Excel Spreadsheet is being created..."
Set uiview=ws.currentView
Set view=uiview.View
Set collection = db.UnprocessedDocuments
Set doc=collection.getFirstDocument
If doc Is Nothing Then
Msgbox "No record found"
End
End If
Set myobject=createObject("Excel.application")
On Error Goto theError
myobject.statusbar="Creating Excel Sheet"
myobject.workbooks.add
Set mysheet=myobject.workbooks(1).Worksheets(1)
mysheet.name="Review"
count=2
Dim fmlaval As Variant
For i = 1 To collection.count
Forall c In view.Columns
mysheet.cells(1, c.Position).value=c.Title
mysheet.cells(1,c.Position).Font.Bold = True
If c.formula <> "" Then
fmla = c.formula
fmlaval = Evaluate(fmla, doc)
Else
item = c.ItemName
fmlaval = doc.GetItemValue( item )
End If
mysheet.cells(count, c.position).value = fmlaval
End Forall
Set doc=collection.getNextDocument(doc)
count=count+1
Next
myobject.statusbar="Finished"
mysheet.Columns.shrinktofit = True
mysheet.Columns.autofit
myobject.visible=True
End
Thank you so much for the help. It helped me a lot. But how do I change the codes so that it will extract only information of wanted rows? (For example only the first two rows). I tried making the “c” argument to only loop until twice, but that didn’t work either. Which part should I change so that it will only take the first two rows of information?
