Domino 9 und frühere Versionen > ND6: Entwicklung

Eknori Excelexport

(1/1)

Thorsten Kalweit:
Hallo zusammen,

erstmal ein riesen Lob an eknori, der mir mit seinem ExportToExcel eine Menge Arbeit und Ärger erspart hat.

Vielen Dank.

Das soll jetzt nicht unverschämt klingen, aber in meinem Fall wäre es ideal, wenn ich die Dokumente selektieren könnte, die exportiert werden sollen.

Wo setze ich im Script an?

--- Code: ---Sub Initialize
Dim Session As New NotesSession ,db As NotesDatabase
Dim sourceview As NotesView,sourcedoc As NotesDocument
Dim dataview As NotesView, dc As NotesDocumentCollection
Dim datadoc As NotesDocument, maxcols As Integer
Dim WS As New Notesuiworkspace
Dim ViewString As String, Scope As String, GetField As Variant
Dim C As NotesViewColumn, FieldName As String, K As Integer,N As Integer
Dim xlApp As Variant, xlsheet As Variant, rows As Integer, cols As Integer
Dim nitem As NotesItem  , entry As NotesViewEntry, vwNav As NotesViewNavigator
Dim ShowView()  As Variant, i As Integer, VList As Variant, ColVals As Variant

Set db = session.CurrentDatabase   'link to current database

         'fetch then display a list of views in the database
Vlist= db.views
K=Ubound(Vlist)  'get size of list
Redim Preserve ShowView(K)
N=-1
For i = 0 To K
If Len(Vlist(i).Name) >0 Then   
FieldName=Trim(Vlist(i).Name)
If Mid(Fieldname,1,1) <>"(" Then  'do not show hidden views
N=N+1                           
ShowView(N) = FieldName
End If
End If 
Next i 
Redim Preserve ShowView(N)
      'now sort the list - by default views are listing in the order that they were created
For i=0 To N
For K=i To N
If  ShowView(i) > ShowView(k) Then
FieldName=ShowView(i)   
ShowView(i) = ShowView(k)
ShowView(k)=FieldName
End If
Next k
Next i 

viewstring= ws.Prompt(PROMPT_OKCANCELLIST,"List of Views","Choose a View","",ShowView )
If Len(viewstring)=0 Then Exit Sub
         'ViewString ="Dan's View"

Set dataview = db.getview(ViewString)  'get selected view

Set vwnav= dataview.createViewnav()

rows = 1
cols = 1
maxcols=dataview.ColumnCount  'how many columns?

Set xlApp = CreateObject("Excel.Application")  'start Excel with OLE Automation
xlApp.StatusBar = "Erstelle Arbeitsmappe. Bitte warten..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)   'select first worksheet

         'worksheet title
'xlsheet.Cells(rows,cols).Value ="View: " + ViewString + ", from Database: " +  db.title +",  Extract created on: " +  Format(Now,"mm/dd/yyyy HH:MM")

xlApp.StatusBar = "Erstelle Überschriften. Bitte warten..."

rows=1  'column headings starts in row 2
For K=1 To maxcols
Set c=dataview.columns(K-1)
xlsheet.Cells(rows,cols).Value = c.title
cols = cols + 1
Next K

Set entry=vwnav.GetFirstDocument
rows=2   'data starts in third row
Do While Not (entry Is Nothing)

For cols=1 To maxcols   
colvals=entry.ColumnValues(cols-1)      'subscript =0
scope=Typename(colvals)
Select Case scope
Case "STRING"
xlsheet.Cells(rows,cols).Value ="'" +  colvals
Case Else       
xlsheet.Cells(rows,cols).Value = colvals
End Select                     
Next cols               
xlApp.StatusBar = "Importiere Lotus Notes Daten:  " & rows-1 '& " of " & dc.count & "." 
rows=rows+1
Set entry = vwnav.getnextdocument(entry)               
Loop

xlApp.Rows("1:1").Select
xlApp.Selection.Font.Bold = True
xlApp.Selection.Font.Underline = True
xlApp.Range(xlsheet.Cells(2,1), xlsheet.Cells(rows,maxcols)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
With xlApp.Worksheets(1)
.PageSetup.Orientation = 2

.PageSetup.centerheader = "Report - Confidential"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
xlApp.StatusBar = "Notesimport abgeschlossen"
         'xlapp.ActiveWorkbook.saveas "c:VX" + Trim(Format(Now,"yyy"))   'save with generated name
dataview.clear 

Set xlapp=Nothing   'stop OLE
Set db=Nothing
End Sub

--- Ende Code ---

dirk_2909:
Ungefähr an den Stellen:


--- Code: ---Set dataview = db.getview(ViewString)  'get selected view
Set vwnav= dataview.createViewnav()
--- Ende Code ---

und


--- Code: ---Set entry=vwnav.GetFirstDocument
rows=2   'data starts in third row
Do While Not (entry Is Nothing)
--- Ende Code ---

Natürlich musst Du ein wenig Mehr bzw. weniger machen:

Aktuelle View ermitteln
markierte Doks ermitteln
über die View-Entries
nur Entries verarbeiten, deren dok auch markiert ist

…

eknori:
Suche dich mal hier http://atnotes.de/index.php/topic,15203.msg128289.html#msg128289 durch, da ist das meines Wissens schon mal umgesetzt worden

Navigation

[0] Themen-Index