Domino 9 und frühere Versionen > Entwicklung
Script Problem
Holger:
Hallo Script Profis,
ausser meinem Summierungsproblem habe ich noch ein Problem mit meinem Script, dem Provider gefällt in diesem nicht, dass dort getnthdocument steht und er hätte gerne getnextdocument, ich habe mit meinen geringen Script Kenntnissen mal probiert das umzuschreiben aber habe es nicht geschafft, das Script hatte ich auch mal hier aus dem Forum und konnte es zwar bezüglich der Formatierung und der Felder umschreiben aber das mit dem getnextDocument kriege ich nicht hin. Weiß jemand von euch rat?
Sub Initialize
Dim ws As New NotesUIWorkspace
Dim s As New NotesSession
Dim collection As NotesDocumentCollection
Set db = s.CurrentDatabase
Set db = s.CurrentDatabase
' On Error Goto Initialize_Error
Set collection = db.UnprocessedDocuments
' Start link to Excel
Set XLApp = CreateObject("excel.application")
If XLApp Is Nothing Then
Error XLAPP_NOT_STARTED
End If
' Add the worksheet
XLApp.Workbooks.Add
Set XLBook = XLApp.Workbooks(XLApp.ActiveWorkbook.Name)
Set Sheet1 = XLBook.Worksheets(1)
Sheet1.Name = "Test_Financials"
intcurrow = 1
Sheet1.Cells(intCurRow, 1).Value = "Project Name"
Sheet1.Cells(intCurRow, 2).Value = "Revenue Increase 2003"
Sheet1.Cells(intCurRow, 3).Value = "Revenue Increase 2004"
Sheet1.Cells(intCurRow, 4).Value = "Revenue Increase 2005"
Sheet1.Cells(intCurRow, 5).Value = "Revenue Increase FY"
Sheet1.Cells(intCurRow, 6).Value = "Cost Saving 2003"
Sheet1.Cells(intCurRow, 7).Value = "Cost Saving 2004"
Sheet1.Cells(intCurRow, 8).Value = "Cost Saving 2005"
Sheet1.Cells(intCurRow, 9).Value = "Cost Saving FY"
Sheet1.Cells(intCurRow, 10).Value = "Investment 2003 Q1"
Sheet1.Cells(intCurRow, 11).Value = "Investment 2003 Q2"
Sheet1.Cells(intCurRow, 12).Value = "Investment 2003 Q3"
Sheet1.Cells(intCurRow, 13).Value = "Investment 2003 Q4"
Sheet1.Cells(intCurRow, 14).Value = "Investment 2004 Q1"
Sheet1.Cells(intCurRow, 15).Value = "Investment 2004 Q2"
Sheet1.Cells(intCurRow, 16).Value = "Investment 2004 Q3"
Sheet1.Cells(intCurRow, 17).Value = "Investment 2004 Q4"
Sheet1.Cells(intCurRow, 18).Value = "Investment 2005 Q1"
Sheet1.Cells(intCurRow, 19).Value = "Investment 2005 Q2"
Sheet1.Cells(intCurRow, 20).Value = "Investment 2005 Q3"
Sheet1.Cells(intCurRow, 21).Value = "Investment 2005 Q4"
Sheet1.Cells(intCurRow, 22).Value = "Investment FY"
Sheet1.Cells(intCurRow, 23).Value = "Operating Cost 2003 Q1"
Sheet1.Cells(intCurRow, 24).Value = "Operating Cost 2003 Q2"
Sheet1.Cells(intCurRow, 25).Value = "Operating Cost 2003 Q3"
Sheet1.Cells(intCurRow, 26).Value = "Operating Cost 2003 Q4"
Sheet1.Cells(intCurRow, 27).Value = "Operating Cost 2004 Q1"
Sheet1.Cells(intCurRow, 28).Value = "Operating Cost 2004 Q2"
Sheet1.Cells(intCurRow, 29).Value = "Operating Cost 2004 Q3"
Sheet1.Cells(intCurRow, 30).Value = "Operating Cost 2004 Q4"
Sheet1.Cells(intCurRow, 31).Value = "Operating Cost 2005 Q1"
Sheet1.Cells(intCurRow, 32).Value = "Operating Cost 2005 Q2"
Sheet1.Cells(intCurRow, 33).Value = "Operating Cost 2005 Q3"
Sheet1.Cells(intCurRow, 34).Value = "Operating Cost 2005 Q4"
Sheet1.Cells(intCurRow, 35).Value = "Operating Cost FY"
intcurrow = 2
' Create the worksheet
For i = 1 To collection.Count
Set doc = collection.GetNthDocument( i )
CreateSheet
Next
Sheet1.Range("A1", "AJ1").Select
xlapp.Selection.Font.Bold = True
xlapp.Selection.Font.Size = 8
xlApp.Selection.Font.ColorIndex = 5
Sheet1.Range("B1", "AJ1").Select
xlapp.Selection.HorizontalAlignment = 3
XlApp.Range("A1:AJ1").Select
With Xlapp.Selection
.Borders(1).Weight = 2
.Borders(2).Weight = 2
.Borders(3).Weight = 2
.Borders(4).Weight = 2
End With
XlApp.Range("A2:AJ2" & Trim(Str(Cell))).Select
With XlApp.Selection
.Borders(1).Weight = 1
.Borders(2).Weight = 1
.Borders(3).Weight = 1
.Borders(4).Weight = 1
End With
Sheet1.Range("A1", "AJ" + Cstr (intcurrow)).Select
XlApp.Columns(1).Columnwidth = 40
XlApp.Columns(2).Columnwidth = 8
XlApp.Columns(3).Columnwidth = 8
XlApp.Columns(4).Columnwidth = 8
XlApp.Columns(5).Columnwidth = 8
XlApp.Columns(6).Columnwidth = 8
XlApp.Columns(7).Columnwidth = 8
XlApp.Columns(8).Columnwidth = 8
XlApp.Columns(9).Columnwidth = 8
XlApp.Columns(10).Columnwidth = 10
XlApp.Columns(11).Columnwidth = 10
XlApp.Columns(12).Columnwidth = 10
XlApp.Columns(13).Columnwidth = 10
XlApp.Columns(14).Columnwidth = 10
XlApp.Columns(15).Columnwidth = 10
XlApp.Columns(16).Columnwidth = 10
XlApp.Columns(17).Columnwidth = 10
XlApp.Columns(18).Columnwidth = 10
XlApp.Columns(19).Columnwidth = 10
XlApp.Columns(20).Columnwidth = 10
XlApp.Columns(21).Columnwidth = 10
XlApp.Columns(22).Columnwidth = 8
XlApp.Columns(23).Columnwidth = 8
XlApp.Columns(24).Columnwidth = 8
XlApp.Columns(25).Columnwidth = 8
XlApp.Columns(26).Columnwidth = 8
XlApp.Columns(27).Columnwidth = 8
XlApp.Columns(28).Columnwidth = 8
XlApp.Columns(29).Columnwidth = 8
XlApp.Columns(30).Columnwidth = 8
XlApp.Columns(31).Columnwidth = 8
XlApp.Columns(32).Columnwidth = 8
XlApp.Columns(33).Columnwidth = 8
XlApp.Columns(34).Columnwidth = 8
XlApp.Columns(35).Columnwidth = 8
XlApp.Columns(36).Columnwidth = 8
xlApp.Columns(2).Wraptext = True
xlApp.Columns(3).Wraptext = True
xlApp.Columns(4).Wraptext = True
xlApp.Columns(5).Wraptext = True
xlApp.Columns(6).Wraptext = True
xlApp.Columns(7).Wraptext = True
xlApp.Columns(8).Wraptext = True
xlApp.Columns(9).Wraptext = True
xlApp.Columns(10).Wraptext = True
xlApp.Columns(11).Wraptext = True
xlApp.Columns(12).Wraptext = True
xlApp.Columns(13).Wraptext = True
xlApp.Columns(14).Wraptext = True
xlApp.Columns(15).Wraptext = True
xlApp.Columns(16).Wraptext = True
xlApp.Columns(17).Wraptext = True
xlApp.Columns(18).Wraptext = True
xlApp.Columns(19).Wraptext = True
xlApp.Columns(20).Wraptext = True
xlApp.Columns(21).Wraptext = True
xlApp.Columns(22).Wraptext = True
xlApp.Columns(23).Wraptext = True
xlApp.Columns(24).Wraptext = True
xlApp.Columns(25).Wraptext = True
xlApp.Columns(26).Wraptext = True
xlApp.Columns(27).Wraptext = True
xlApp.Columns(28).Wraptext = True
xlApp.Columns(29).Wraptext = True
xlApp.Columns(30).Wraptext = True
xlApp.Columns(31).Wraptext = True
xlApp.Columns(32).Wraptext = True
xlApp.Columns(33).Wraptext = True
xlApp.Columns(34).Wraptext = True
xlApp.Columns(35).Wraptext = True
xlApp.Columns(36).Wraptext = True
xlApp.Columns(1).VerticalAlignment = 1
xlApp.Columns(2).VerticalAlignment = 1
xlApp.Columns(3).VerticalAlignment = 1
xlApp.Columns(4).VerticalAlignment = 1
xlApp.Columns(5).VerticalAlignment = 1
xlApp.Columns(6).VerticalAlignment = 1
xlApp.Columns(7).VerticalAlignment = 1
xlApp.Columns(8).VerticalAlignment = 1
xlApp.Columns(9).VerticalAlignment = 1
xlApp.Columns(10).VerticalAlignment =1
xlApp.Columns(11).VerticalAlignment =1
xlApp.Columns(12).VerticalAlignment =1
xlApp.Columns(13).VerticalAlignment =1
xlApp.Columns(14).VerticalAlignment =1
xlApp.Columns(15).VerticalAlignment =1
xlApp.Columns(16).VerticalAlignment =1
xlApp.Columns(17).VerticalAlignment =1
xlApp.Columns(18).VerticalAlignment =1
xlApp.Columns(19).VerticalAlignment =1
xlApp.Columns(20).VerticalAlignment =1
xlApp.Columns(21).VerticalAlignment =1
xlApp.Columns(22).VerticalAlignment =1
xlApp.Columns(23).VerticalAlignment =1
xlApp.Columns(24).VerticalAlignment =1
xlApp.Columns(25).VerticalAlignment =1
xlApp.Columns(26).VerticalAlignment =1
xlApp.Columns(27).VerticalAlignment =1
xlApp.Columns(28).VerticalAlignment =1
xlApp.Columns(29).VerticalAlignment =1
xlApp.Columns(30).VerticalAlignment =1
xlApp.Columns(31).VerticalAlignment =1
xlApp.Columns(32).VerticalAlignment =1
xlApp.Columns(33).VerticalAlignment =1
xlApp.Columns(34).VerticalAlignment =1
xlApp.Columns(35).VerticalAlignment =1
xlApp.Columns(36).VerticalAlignment =1
With XlApp.Worksheets(1)
.PageSetup.Zoom = 60
.PageSetup.Orientation = 2
.PageSetup.PrintTitleRows = "$1:$1"
.Pagesetup.centerheader="Test Financials- Confidential"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
Print "Saving excel spreadsheet as C:\Test_Financials.xls"
XLBook.SaveAs ("C:\Test_Financials.xls")
Sheet1.Range("A2").Select
xlApp.Visible = True
' XLBook.Close
Initialize_Exit:
' XLApp.Quit
' Set Sheet1 = Nothing
' Set XLBook = Nothing
' Set XLApp = Nothing
' Print "Completed"
Exit Sub
Initialize_Error:
Beep
Select Case Err
Case XLAPP_NOT_STARTED
strErrMsg = "Excel Application could not be started."
Case TEMP_FILE_PATH_NOT_SPECIFIED
strErrMsg = "A 'Temporary File Path' entry was not found in the Keywords view."
Msgbox "The following error occurred in the Worksheet Process." & CRLF & "Error No. " & Cstr(Err) & " - " & strErrMsg,,"An Application Error Has Occurred"
Resume Initialize_Exit
End Sub
eknori:
Dann kannst du :
For i = 1 To collection.Count
Set doc = collection.GetNthDocument( i )
CreateSheet
Next
ersetzen durch:
Set doc = collection.GetFirstDocument
If Not doc Is Nothing Then
CreateSheet
Set doc = collection.GetNextDocument (doc)
End If
eknori
Holger:
Super Eknori, Danke das funktioniert.
Gruß
Holger
Holger:
Hallo
ich bin es nochmal, habe jetzt doch ein Problem, der Agent export mir jetzt nur noch ein Dokument nach Excel obwohl ca. 15 in der Db stehen. Vorher hat er alle exportiert, woran könnte das liegen?
Gruß
Rob Green:
ich kenne eknori´s IF Schleifenkonstrukt zwar so noch nicht, aber nimm mal zB stattdessen eine WHILE Schleife
While Not (doc Is Nothing)
CreateSheet...
....
Set doc = collection.GetNextDocument (doc)
Wend
dann springt beim "set doc=..." die Vearbeitung wieder ins "While Not..", bis eben kein Doc mehr gefunden wird zum abarbeiten.
Navigation
[0] Themen-Index
[#] Nächste Seite
Zur normalen Ansicht wechseln