Hi, habe folgendes Script.
Da ich aber keine Abfrage haben möchte welche Exceldatei ich verwenden will, habe ich mir gedacht gebe ich die Datei einfach als String an .
So jetzt kommst. err = 213
Sub Initialize
Dim db As NotesDatabase
Dim view As NotesView
Dim viewname As String
Dim doc As NotesDocument
Dim column As NotesViewColumn
Dim nbcol As Integer
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim strFileName As String '*255
Dim strTitle$
Dim strFilter$
Dim Handle As Variant
Dim WBook As Variant
Dim Wsheet As Variant
Dim doc2 As NotesDocument
Dim p As Long
Dim Bovie As String
On Error Goto generalerrorhandler
On Error 213 Resume Next
Dim session As New NotesSession
'File Dialog
'Excel Application
Set db = session.CurrentDatabase
'viewname = session.GetEnvironmentString("NameOfView")
Set view = db.GetView("ExReport") 'db.GetView(viewname)
'File Dialog
strFileName = Chr(0)
strFileName="f:\import\REPORT_Vorlage.xls"
strTitle$ = "Select Excel file to export to."
strFilter$ = "MS Excel Files|*.xls|All Files|*.*|" 'Use this format for ANY file type
%REM
If IsDefined ("WIN32") Then
If W32_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"| 'We need to do this because the return is a NULL terminated string.
Else 'The user chose to Cancel the operation so exit the subroutine
Exit Sub
End If
Elseif IsDefined ("WIN16") Then
If W16_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"|
Else 'The user chose to Cancel the operation so exit the subroutine
Exit Sub
End If
Else
Msgbox "Cannot load file dialog window on this operating system." & Chr(13) & "Process Terminated",0+64,"Error"
Exit Sub
End If
%END REM
'Open Excel Application
Set Handle = CreateObject("Excel.Application")
Set WBook = Handle.Workbooks.Open(StrFileName)
If Err = 213 Then
Set WBook = Handle.Workbooks.Add
Else
If Msgbox ("The export to Excel is about to begin. All existing spreadsheet contents will be overwritten!" & Chr(13) & Chr(13) & "Do you wish to proceed?",4+48,"Export to Excel") = 7 Then
WBook.Close
Handle.DisplayAlerts = True
Handle.Quit
Set Handle = Nothing
Exit Sub
End If
End If
'Handle.Visible = True 'Uncomment if you wish Excel to be seen
Set Wsheet = WBook.Application.Workbooks(1).Worksheets(4)
Handle.DisplayAlerts = False
'Clear contents of worksheet - Method 1...
'Delete and then re-add the worksheet itself
'Wsheet.Delete
'WBook.Worksheets.Add
'Set Wsheet = WBook.Application.Workbooks(1).Worksheets(1)
'Clear contents of worksheet - Method 2...
'By not specifying a range for the Cells property, all the contents in all the cells on the worksheet will be erased
Wsheet.Cells.ClearContents
nbcol = Ubound(view.Columns) 'Determine the number of columns in the view
'Progress Bar
'Create a count of all the documents in this view. This will be used to set the upper bound for the Progress Bar
Set doc2 = view.GetFirstDocument
p = 0
While Not (doc2 Is Nothing)
p = p + 1
Set doc2 = view.GetNextDocument(doc2)
Wend
Dim pb As New LNProgressBar(True)
Call pb.SetText("Exporting View to Excel Spreadsheet." & Chr(13) & Chr(13),"Please wait...")
'We set the range of the Progress Bar to p elements
Call pb.SetProgressRange(p)
'Begin looping through the documents in the view and add them into the Excel worksheet starting on row 3. Change the variable "i" to start on a different row.
'Remember that the column headings will occupy row one.
i = 3
k = 0
Set doc = view.GetFirstDocument
While Not (doc Is Nothing)
Call pb.SetProgressPos(i - 3)
For k = 0 To nbcol 'Populate additional rows and columns in Excel spreadsheet from Notes documents in view
Wsheet.Cells(i,k + 1).Value = doc.ColumnValues(k)
Next
i = i + 1
Set doc = view.GetNextDocument(doc)
Wend
'Create column headings in Excel spreadsheet
j = 0
Do
Set column = view.Columns(j)
Wsheet.Cells(1, j + 1).Value = column.Title
j = j + 1
Loop Until j = nbcol + 1
'Perform formatting in the Excel spreadsheet
Wsheet.Rows(1).Font.Bold = True
Wsheet.Rows(1).Font.Size = 12
Wsheet.Columns(7).NumberFormat = "################" 'Formats the seventh column to display the account numbers properly. Comment out or modify for your specific needs.
'Other number formats can include: "$#,##0.00" or "hh:mm:ss". See the "Vbaxl8.hlp" help file for more information.
For k = 1 To nbcol + 1
Wsheet.Columns(k).Autofit
Next
'Terminate the progress bar
Call pb.PBDelete
'Close Excel application
Handle.ActiveWorkbook.SaveAs strFileName
Handle.ActiveWorkbook.Close
Handle.DisplayAlerts = True
Handle.Quit
Set Wsheet=Nothing
Set Wbook=Nothing
Set Handle=Nothing
Msgbox "Export to Excel completed successfully.",0+64,"Export Complete"
Exit Sub
generalerrorhandler:
Msgbox "Error " & Err() & ": " & Error(),0+64,"Error"
If Not (pb Is Nothing) Then
Call pb.PBDelete
End If
WBook.Close
Handle.DisplayAlerts = True
Handle.Quit
Set Handle = Nothing
Exit Sub
End Sub
