Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exporting Lotus Notes Data to Excel

Posted on 2004-04-30
7
Medium Priority
?
1,658 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

971 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