Solved

Exporting Lotus Notes Data to Excel

Posted on 2004-04-30
7
1,648 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

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…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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