23. november 2004 - 15:50Der er
12 kommentarer og 1 løsning
Overføre data fra agent til Excel
Hejsa
Jeg har en lille agent der henter nogle data, og dem vil jeg så gerne have flyttet over i Excel - så jeg kan stille dem pænt op og lave en sammenregning. Min agent danner ca. 30 linier som pt. bliver udskrevet i en messagebox. Nogen der kan guide mig igennem en eksport til Excel???
Kkode til agenten:
Sub Initialize Dim Session As New NotesSession Dim Db As NotesDatabase Dim ProfileDoc As NotesDocument Dim UserArr As Variant Dim S As String Dim N As Integer
Set Db = Session.CurrentDatabase
REM Get user info UserArr = Evaluate(|@Unique(@DbColumn("":"NoCache";"";".LookupTimeSheetsByAssignee";1))|)
S = "Salary group ¤ User ¤ Flex saldo ¤ Flex saldo last" & Chr$(10) For N = 0 To Ubound(UserArr) Set ProfileDoc = Db.GetProfileDocument("Flex" & UserArr(N)) S = S & Chr$(10) S = S & ProfileDoc.GetItemValue("Prefix")(0) & " ¤ " S = S & ProfileDoc.GetItemValue("User")(0) & " ¤ " S = S & Cstr(ProfileDoc.GetItemValue("Flex")(0)) & " ¤ " S = S & Cstr(ProfileDoc.GetItemValue("FlexLast")(0)) & " ¤ "
Denne eksporterer et view til Excel... du kan bare arbejde videre med den...
Sub Initialize 'Create an Excel Spreadsheet from any view '11/3/2000 Art Yates 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 = "Creating WorkSheet. Please be patient..." 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 = "Creating Column Heading. Please be patient..."
rows=1 'column headings starts in row 1 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 second 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 = "Importing Notes Data - Document " & 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.Range(xlsheet.Cells(2,1), xlsheet.Cells(rows,maxcols)).Select ' xlApp.Selection.Font.Name = "Arial" ' xlApp.Selection.Font.Size = 9 xlApp.Selection.Columns.AutoFit ' xlApp.Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= True, Alignment:=True, Border:=True, Pattern:=True, Width:=True With xlApp.Worksheets(1) .PageSetup.Orientation = 2 .PageSetup.centerheader = "" .Pagesetup.RightFooter = "Side &P" & Chr$(13) & "Printet: &D" .Pagesetup.CenterFooter = "" End With xlApp.ReferenceStyle = 1 xlApp.Range("A1").Select xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed." '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
Godt spørgsmål (kommer nu lidt sent til at tænke på at jeg håber ikke der er rettighedsproblem ved dette).... Jeg er 99,9% sikker på at det kommer fra Lotus Sandbox'en, men det er nok omkring 3 år siden jeg fandt det til et projekt.. Det var før "copy as table" som kom med Notes 6. ;-)
Ifølge Google har den været publiceret af Art Yates selv på SearchDomino.com. Jeg kan ikke lige finde mit login dertil - så jeg kan ikke se selve siden - men det må være denne...
Export any view to Excel ... Code Sub Initialize 'Create an Excel Spreadsheet from any view '11/3/2000 Art Yates Dim Session As New NotesSession ,db As NotesDatabase Dim sourceview As ... searchdomino.techtarget.com/ tip/1,289483,sid4_gci770476,00.html - 36k -
Vil du have exporteret til excell i rækker med 3 kolonner for hver løkke du har i dit script ? Så har et script som er knap så omfattende som wise's, selvom det også er fint.
Så er det sgu ved at være tæt på det virker - det jeg mangler er dog at summere nogle felter, men jeg kan ikke finde en metode til at sige, at et givent felt skal summere andre felter....
Dette script henter værdier fra kolonne 1 og 2 i et view og opretter samme i excell. Bemærk at har du en engelsk version af excell skal "Ark1" ændres til "Sheet1" (gik sur i objektnavnet) Du kan så bare sætte celleværdierne til dine beregnede variabler istedet (wsdata.Cells(count,1).Value = S): Dim excelapp As Variant Dim wsdata As Variant Dim wsgrafik As Variant
Dim session As New NotesSession Dim db As NotesDatabase Dim nav As NotesViewNavigator Dim view As NotesView Dim entry As NotesViewEntry Dim doc As NotesDocument Dim count As Integer
Set view = db.GetView("DitViewNavn") Set nav = view.CreateViewNav
Set entry = nav.GetFirst
count = 0
Do While Not (entry Is Nothing)
If entry.ColumnValues(1) <> "" Then count = count + 1 wsdata.Cells(count,1).Value = entry.ColumnValues(1) wsdata.Cells(count,2).Value = entry.ColumnValues(2) End If
sbay >> vedr. summer, mener du summer i Excel? Prøv at tænde for Makro-recorder og optag dannelsen af en sum. Det burde være muligt at udlede syntaksen herfra.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.