asked on

Exporting Lotus Notes Data to Excel

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)

qwaletee

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!

crystalsingleton

ASKER

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.

ASKER CERTIFIED SOLUTION

HemanthaKumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HemanthaKumar

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

crystalsingleton

ASKER

When I create the agent am I using lotus script?

HemanthaKumar

Agent can have LS or Formula or Java

PaulCutcliffe

An yes, you should be selecting LotusScript to enter any of the code offered here.