Solved

Exporting Lotus Notes Data to Excel

Posted on 2004-04-30
7
1,652 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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