Accessing Large amounts of data contained in a spread sheet dynamically from within Notes.

Posted on 2004-04-29
Last Modified: 2013-12-18

We have a internal product request system in Lotus Notes.  At the moment the only way we have of looking up products is to post a spread sheet (obtained from another old system) in a Notes Document and provide a link in the application form to the spread sheet doc.  This requires the user to follow the link then launch the spreadsheets and then type thier product number to get the product details which then have to be copied back into the original document.

This is quite a tedious process and obviosly not ideal.  I'm looking for a way to somehow automatically lookup the details when the produuct number is entered.  Either somehow geting the  data from the spread sheet into Notes in an easy manner on a rountine basis (There is a lot data though!) or providing a lookup to the spread sheet (which can be updated easily enough).

Any ideas?



Ps I've been generous with the points! ;-)
Question by:IanWood
LVL 19

Expert Comment

ID: 10947361
this can be done once u provide a link or attach(excel) to the document .

But we need to know the structure of Excel how ur maintaing and for which fields it should be updated.
LVL 19

Accepted Solution

madheeswar earned 250 total points
ID: 10947366
A sample code:
Sub Initialize
 Dim FileNum As Integer
 Dim xlFilename As String

 Filenum% = Freefile()
 xlFileName$ = Inputbox("What file name and path? example:H:

 Dim session As New NotesSession
 Dim db As NotesDatabase
 Dim view As NotesView
 Dim doc As NotesDocument
 Set db = session.CurrentDatabase
 Set doc = New NotesDocument(db)
 Dim One As String

 Dim row As Integer
 Dim written As Integer

 '// Next we connect to Excel and
open the file. Then start pulling over the records.
 Dim Excel As Variant
 Dim xlWorkbook As Variant
 Dim xlSheet As Variant
 Print "Connecting to Excel..."
 Set Excel = CreateObject( "Excel.Application.8" )
 Excel.Visible = False '// Don't display the Excel window
 Print "Opening " & xlFilename & "..."
 Excel.Workbooks.Open xlFilename '// Open the Excel file
 Set xlWorkbook = Excel.ActiveWorkbook
 Set xlSheet = xlWorkbook.ActiveSheet

 '// Cycle through the rows of the
Excel file, pulling the data over to Notes
 Goto Records
 Print "Disconnecting from Excel..."
 xlWorkbook.Close False '// Close the Excel file without saving (we
made no changes)
 Excel.Quit '// Close Excel
 Set Excel = Nothing '// Free the memory that we'd used
 Print " " '// Clear the status line

 row = 0 '// These integers intialize to zero anyway
 written = 0

 Print "Starting import from Excel file..."

 Do While True
 With xlSheet
 row = row + 1
 Set view = db.GetView("Main View")
 Set doc = db.CreateDocument '// Create a new doc
 doc.Form = "ImportForm1"

 If .Cells (row, 1).Value = "" And .Cells(row,2).Value = ""
And .Cells (row, 3).Value = "" And .Cells(row,4).Value = "" And .Cells
(row, 5).Value = "" And .Cells(row,6).Value = "" And .Cells (row, 7).Value
= "" And .Cells(row,8).Value = "" And .Cells (row, 9).Value = "" And
.Cells(row,10).Value = ""Then
 Goto Finish
 End If

 If .Cells (row, 1).Value = "PO #" And .Cells(row,2).Value
= "Order #" And .Cells (row, 3).Value = "Order da" And .Cells(row,4).Value
= "Part #" And .Cells (row, 5).Value = "Or" And .Cells(row,6).Value
= "Line " And .Cells (row, 7).Value = "Qty" And .Cells(row,8).Value
= "Unit pri" And .Cells (row, 9).Value = "Ship to Company" And
.Cells(row,10).Value = "Ship method"Then
 Goto Finish
 End If

 doc.SWEPO = .Cells( row, 1 ).Value
 doc.SWEORDER = .Cells(row, 2 ).Value
 doc.SWEORDERDATE = .Cells(row, 3).Value
 doc.ITEMNUMBER = .Cells( row, 4 ).Value
 doc.ORDERSTATUS = .Cells(row, 5).Value
 doc.QUANTITYORDERED = .Cells( row, 6).Value
 doc.AMOUNTBILLED = .Cells(row, 7).Value
 doc.SHIPMETHOD = .Cells( row, 8).Value
 doc.SHIPDATE = .Cells(row, 9).Value
 doc.TRACKINGNUMBER = .Cells(row, 10).Value

 Call doc.Save( True, True ) '// Save the new doc

 written = written + 1
 Print written
 If written = 5 Then
 Print written
 Goto Finish
 Print written
 Messagebox "Finished"
 Goto Done
 End If
 End With
End Sub
LVL 19

Expert Comment

ID: 10947368
This imports data from Excel into Lotus Notes, skipping blank lines and column headers. This will only import 100 records at a time and can be changed by changing the written value.
Technology Partners: 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!

LVL 24

Expert Comment

ID: 10948203
Use Notes FX method...

I have given similar soln in this forum , let me find you the link

LVL 24

Expert Comment

ID: 10948215

Author Comment

ID: 10948349
Thanks for that, I'd had a look and it's another option..
LVL 24

Expert Comment

ID: 10948951
FX is more easy to implement and maintain.. Absolutely no coding.. And is handled native to the software
LVL 31

Expert Comment

ID: 10950383
You might also consider having Notes automatically access the legacy system, instead of using a SS as intermediary.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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…
For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

685 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