I’m attempting to export docs to excel based of a key. My export isn’t excactly working correctly. I don’t think it’s selecting the fields off each doc correctly and looping.
Also, I’m not sure if I’m doing this correctly since the fields on the notes documents are text fields and some have multiple values in the field, so I’m not sure how the excel will handle this. I was hoping to display each value of the field in a different cell going down the page, then skip to the next document. It would be hard to read 30 values in one cell divided by commas. I read something about using ColumnValues but not sure that is the way to go. I’ve looked at many samples and still not sure on my code. Any suggestions?
Thank you!!!
Set collection = view.GetAllDocumentsByKey(key,True)
Print "Connecting to Excel..."
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Set Excel = CreateObject( "Excel.Application" )
Excel.Visible = False
Excel.Workbooks.Add
Set xlWorkbook = Excel.ActiveWorkbook
Set xlsheet = Excel.Workbooks(1).Worksheets(1)
Excel.Windows(1).DisplayGridlines=True
Excel.ScreenUpdating = False
Excel.Calculation = -4135 'Calculation Manual
row = 1
With xlsheet
.Cells(row, 1).Value = "Part"
.Cells(row, 2).Value = "Description"
.Cells(row, 3).Value = "Location"
.Cells(row, 4).Value = "Roles"
.Cells(row, 5).Value = "Number"
End With
Set Doc = collection.GetFirstDocument
row = 2
Do Until Doc Is Nothing
If collection.count > 0 Then
With xlsheet
.Cells(row, 1).Value = doc.Part(0)
.Cells(row, 2).Value = doc.Descrioption(0)
.Cells(row, 3).Value = doc.Location(0)
.Cells(row, 4).Value = doc.Roles(0) .Cells(row, 5).Value = doc.Number(0) End With
row = row + 1
End If
Set Doc = collection.GetNextDocument(Doc)
Loop
Excel.ScreenUpdating = True
Excel.Calculation = -4105
Excel.Visible = True
End Sub
Is just the order of items wrong? If so, it might be that the order in which the documents appear in the view is not the same as in the document collection returned by GetAllDocumentsBy Key
Did you check collaction.count > 0 (I’m sure you did, but just in case…)
Have you tried to run the script without the lines that prepare excel performance (ScreenUpdating etc.)?
Yes, the count is showing, however I don’t think it’s correct. It should only show 3 documents, right now and the count shows 12.
Yes, 12 lines export to excel but it’s not correct. Most of the fields will have one value but one there are some that have maybe 20 values. The excel file will not be readable if all the values are in one cell. In notes, the fields go across the page then down. In excel all the rows would need one value in each cell then the one cell with many values should go down the page, then once done with the doc, get the next document. I’m not sure how this will work in excel. In notes, it’s normal since the the text field returns to the next row, for each value.
Does that make more sense? This is a hard one to figure out.
sorry that I get back to you so late, but I time was lacking for visiting this forum.
You can be pretty sure that collection.count returns the correct number of documents NotesView.GetAllDocumentsByKey finds in the view. So I presume 12 is the correct number. If that is not correct, i.e. 3 should be correct, then there is a problem that has nothing to do with excel but maybe the view.
Now as soon as you got the number of documents that are returned to the collection straight, tackle the export of multi value fields to excel as follows:
In Excel, multiple cells can be handled using the Range object.
You first obtain a Range via the select method (please see VBA for excel documentation) and the iterate over the cells in that range using a forall loop.
Forall Zelle In Range
If m <= Ubound(Notesitem.Values) Then Zelle.Value = Notesitem.Values(m)
m=m+1
End Forall
A problem is always the newline character (Chr(10)). It is not properly exported and you have to find some workaround for that if your fields contain newline characters. I did it like this, but there might be much better solutions out there.
Set a = doc.getFirstItem(Fieldname)
If a.Type = TEXT And Ubound(a.Values) = 0 Then
b = Evaluate("@replaceSubstring("+Fieldname+";@Newline;""##"")",doc)
Range.Value = b(0)
Call Range.Replace("##",Chr(10))
End if
I have something so that it will print going down each row, but there is a lot more code, since it can work on any dB and you would not have to hard-code the values.
You may simply want to read to code and then figure out how to do it for your example. In case if interested in the code drop me a line at my forum email address.