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

x
?
Solved

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

Posted on 2004-04-29
8
Medium Priority
?
221 Views
Last Modified: 2013-12-18
Hi

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?

Cheers

Ian

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

Expert Comment

by:madheeswar
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.
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 1000 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:
\June282001.xls")

 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


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

 Print "Starting import from Excel file..."

 Do While True
Finish:
 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
 Else
 Print written
 Messagebox "Finished"
 Goto Done
 End If
 End With
 Loop
 Return
Done:
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10948203
Use Notes FX method...

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

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10948215
0
 

Author Comment

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

Expert Comment

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

Expert Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
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 summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

885 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