Solved

Exporting Lotus Notes Data to Excel

Posted on 2004-04-30
7
1,643 Views
Last Modified: 2013-12-18
Can someone help me, I'm kinda of a novice to programming to pls bear with me.  

I recieved a code from here to export lotus notes data to excel, however it only exports the view that I set up.. I need to actually export the data in the view.. such as if a client fills out a form.. How can I get that data over to excel with my current action button..

Here's the code that I copy from this site.


Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim WS As New Notesuiworkspace
      Dim v As NotesView
      Dim UiView As notesuiview
      Dim ViewString As String
      Dim xl As Variant
      Dim xlSheet As Variant
      
      Const View$="Script Changes"
      Const ExportItem$="Export"
      Const xlsSave$="False"
      Const xlsFileName$="c:\temp\Script Changes Export.xls"
      
      Set db=session.CurrentDatabase
      Set v=db.GetView(View$)
      Set xl=CreateObject("Excel.Application")
      xl.Workbooks.Add
      Set xlSheet = xl.Workbooks(1).Worksheets(1)
      
      col!=1
      Forall vColumn In v.Columns
            xlSheet.Cells(1, col!)=vColumn.Title
            col!=col!+1
      End Forall
      
      Dim docX As NotesDocument
      Set docX=v.GetFirstDocument
      row!=2
      While Not docX Is Nothing
            col!=1
            Set item=docX.GetFirstItem(ExportItem$)
            'Process=False
            'If item Is Nothing Then
            '      Process=True
            'Elseif item.Text="" Then
            '      Process=True
            'End If
            If Process=True Then
                  Forall cValue In docX.ColumnValues
                        xlSheet.Cells(row!, col!)=cValue
                        col!=col!+1
                  End Forall
                  row!=row!+1
            End If
            Call docX.ReplaceItemValue(ExportItem$, Now)
            Call docX.Save (True, True)
            Set docX=v.GetNextDocument(docX)
0
Comment
Question by:crystalsingleton
7 Comments
 
LVL 31

Expert Comment

by:qwaletee
ID: 10961929
Hi crystalsingleton,

          'Process=False
          'If item Is Nothing Then
          '     Process=True
          'Elseif item.Text="" Then
          '     Process=True
          'End If

You need to uncomment these lines.  The lines in the IF PROCESS=TRUE block do the export.  You never set process=true, because the commented-out code is what does that.  I don't necessarily agree with the way the code is constructed, but it should work.


Cheers!
0
 

Author Comment

by:crystalsingleton
ID: 10962789
oh the code works , I just need something to add to it to change what it exports out.. right now it's exporting the view, I need the data inside the view.
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 500 total points
ID: 10963959
Do you mean selected documents in the view ??

If so create an agent to run on selected documents and paste this code... Call this agent in the view action button..

     Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim WS As New Notesuiworkspace
     Dim v As NotesView
     Dim UiView As notesuiview
     Dim ViewString As String
     Dim xl As Variant
     Dim xlSheet As Variant
Dim coll as NotesDocumentCollection    
     Const View$="Script Changes"
     Const ExportItem$="Export"
     Const xlsSave$="False"
     Const xlsFileName$="c:\temp\Script Changes Export.xls"
     
     Set db=session.CurrentDatabase
set coll = db.UnprocessedDocuments
     Set v=db.GetView(View$)
     Set xl=CreateObject("Excel.Application")
     xl.Workbooks.Add
     Set xlSheet = xl.Workbooks(1).Worksheets(1)
     
     col!=1
     Forall vColumn In v.Columns
          xlSheet.Cells(1, col!)=vColumn.Title
          col!=col!+1
     End Forall
     
     Dim docX As NotesDocument
     Set docX=coll.GetFirstDocument
     row!=2
     While Not docX Is Nothing
          col!=1
          Set item=docX.GetFirstItem(ExportItem$)
          'Process=False
          'If item Is Nothing Then
          '     Process=True
          'Elseif item.Text="" Then
          '     Process=True
          'End If
          If Process=True Then
               Forall cValue In docX.ColumnValues
                    xlSheet.Cells(row!, col!)=cValue
                    col!=col!+1
               End Forall
               row!=row!+1
          End If
          Call docX.ReplaceItemValue(ExportItem$, Now)
          Call docX.Save (True, True)
          Set docX=coll.GetNextDocument(docX)

~Hemanth
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10963993
If you don't want the column values but rather the field values then try this

     Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim WS As New Notesuiworkspace
     Dim v As NotesView
     Dim UiView As notesuiview
     Dim ViewString As String
     Dim xl As Variant
     Dim xlSheet As Variant
     
     Const View$="Script Changes"
     Const ExportItem$="Export"
     Const xlsSave$="False"
     Const xlsFileName$="c:\temp\Script Changes Export.xls"
     
     Set db=session.CurrentDatabase
     Set v=db.GetView(View$)
     Set xl=CreateObject("Excel.Application")
     xl.Workbooks.Add
     Set xlSheet = xl.Workbooks(1).Worksheets(1)
     
     col!=1
     Forall vColumn In v.Columns
          xlSheet.Cells(1, col!)=vColumn.Title
          col!=col!+1
     End Forall
     
     Dim docX As NotesDocument
     Set docX=v.GetFirstDocument
     row!=2
     While Not docX Is Nothing
          col!=1
          Set item=docX.GetFirstItem(ExportItem$)
          'Process=False
          'If item Is Nothing Then
          '     Process=True
          'Elseif item.Text="" Then
          '     Process=True
          'End If
          If Process=True Then
cvalue = docx.Field1(0)
Gosub SetValues
cvalue = docx.Field2(0)
Gosub SetValues
cvalue = docx.Field3(0)
Gosub SetValues
cvalue = docx.Field4(0)
Gosub SetValues
' And add any more fields that should be exported to xl

          End If
          Call docX.ReplaceItemValue(ExportItem$, Now)
          Call docX.Save (True, True)
          Set docX=v.GetNextDocument(docX)
     Wend

Exit sub

SetValues:
xlSheet.Cells(row!, col!)=cValue
col!=col!+1
row!=row!+1
Return

End Sub
0
 

Author Comment

by:crystalsingleton
ID: 10965243
When I create the agent am I using lotus script?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10967722
Agent can have LS or Formula or Java
0
 

Expert Comment

by:PaulCutcliffe
ID: 11069083
An yes, you should be selecting LotusScript to enter any of the code offered here.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now