Manage Learn to apply best practices and optimize your operations.

Send Data To And From Lotus Notes With Ms Excel

You can send data to and from Lotus Notes via MS Excel.

I created a form (PortalOrder) in Notes with 5 fields (OrderNumber, Telephone,
ForwardTo, Ring and CompositeBill) and then created a view (PortalOrders) to be
able to search by a unique field (OrderNumber).
The spreadsheet contains information to create the document in Notes. Four
fields are sent to create the document in Notes (OrderNumber, Telephone,
ForwardTo and Ring).
The spreadsheet row is updated with the text "Processed [date/time]" to tell
that it was sent to Notes. This is so documents will not be re-sent which
would create duplicate documents.
The field CompositeBill can then be updated in the Notes application. The
CompositeBill field can then be retrieved from the Notes database and updated
in the spreadsheet.

I assigned the SendOrders and GetOrderInfo subroutines to macros so they could
be run with Shift+Ctrl+S and Shift+Ctrl+G respectively.

Layout of Excel columns:
Col 1: OrderNumber (Sent to Notes)
Col 2: Telephone (Sent to Notes)
Col 3: ForwardTo (Sent to Notes)
Col 4: Ring (Sent to Notes)
Col 5: Processed (updated after info sent)
Col 6: CompositeBill (Returned from Notes)

This is the code to put in a module in MS Excel that will allow you to send and
receive data between Excel and Notes.
----code starts here-----------------------------
'Constants used in these routines...

'change these for your server name and database (add path if not in default
area of Notes)
Const cNotesServerName = "ServerName"
Const cNotesDatabase = "NotesDB.nsf"
Const cWorksheet = "Sheet1"
'row starting point for data
Const cStartingRow = 8

'Type definition for data passing to and from Lotus Notes
Type ESPData
OrderNumber As String
Telephone As String
ForwardTo As String
Ring As String
CompositeBill As String
End Type

Sub SendOrders()
'
' SendOrders Macro
' Macro recorded 11/11/1999 by Steve Hochreiter
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Call SendESPOrders

End Sub

Sub GetOrderInfo()
'
' GetOrderInfo Macro
' Macro recorded 11/11/1999 by Steve Hochreiter
'
' Keyboard Shortcut: Ctrl+Shift+G
'

Call GetESPOrderInfo

End Sub

Private Sub SendESPOrders()

Dim MyData As ESPData
Dim Processed As String

Dim Row As Integer
Dim R As Integer
Dim LastRow As Integer

'starting point
Row = cStartingRow

'get where the data ends
LastRow = FindLastRow()

'Loop thru the spreadsheet rows
For R = Row To LastRow - 1
With Worksheets(cWorksheet).Cells(R, 5)
Processed = .Value
End With
If Processed = "" Then 'has not been sent to Notes
'put data from spreadsheet cells in the typearray to pass
With Worksheets(cWorksheet).Cells(R, 1)
MyData.OrderNumber = .Value
End With
With Worksheets(cWorksheet).Cells(R, 2)
MyData.Telephone = .Value
End With
With Worksheets(cWorksheet).Cells(R, 3)
MyData.ForwardTo = .Value
End With
With Worksheets(cWorksheet).Cells(R, 4)
MyData.Ring = .Value
End With

'create the Lotus Notes document from the data we pass
Call CreateDoc(MyData)

'update the row with an indicator that it was sent to Notes
With Worksheets(cWorksheet).Cells(R, 5)
.Value = "Processed " & Format(Now(), "dddd, mmm d, yyyy hh:mm
AMPM")
End With
End If 'Processed = ""
Next R

End Sub

Public Sub CreateDoc(MyData As ESPData)
'create a document in Lotus Notes from the data that is passed in
Dim Session As Object
Dim db As Object
Dim ESPDoc As Object

'Create the session. This will cause Lotus notes to open in the background if
it is not running.
Set Session = CreateObject("Notes.NotesSession")

'Create a handle to the Lotus Notes database using the session created above.
Set db = Session.GetDatabase(cNotesServerName, cNotesDatabase)

'Create a new document
Set ESPDoc = db.CreateDocument()

'Set some of the fields on the document.
ESPDoc.Form = "PortalOrder"
ESPDoc.OrderNumber = MyData.OrderNumber
ESPDoc.Telephone = MyData.Telephone
ESPDoc.ForwardTo = MyData.ForwardTo
ESPDoc.Ring = MyData.Ring
'Make the form compute to update the fields
Call ESPDoc.ComputeWithForm(False, False)
'Save the document.
Call ESPDoc.Save(True, True)

End Sub

Public Sub GetDoc(MyData As ESPData)
Dim Session As Object
Dim db As Object
Dim view As Object
Dim ESPDoc As Object

'Create the session. This will cause Lotus notes to open in the background if
it is not running.
Set Session = CreateObject("Notes.NotesSession")

'Create a handle to lotus notes database using the session created above.
Set db = Session.GetDatabase(cNotesServerName, cNotesDatabase)
'Create a handle to the view we need
Set view = db.GetView("PortalOrders")
'create a handle to the document we need
This was last published in November 2000

Dig Deeper on Domino Resources - Part 7

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchExchange

SearchContentManagement

Close