Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Importing Excel Data using Lotus Notes

Posted on 2004-05-02
23
Medium Priority
?
579 Views
Last Modified: 2013-12-18
does anybody have a script/ formula in uploading excel data using lotus notes?
0
Comment
Question by:emem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
23 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 10974896
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


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
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10974901
You can also search this forum, you know, this has been asked at least a 1000 times before ....

cheers,

Tom
0
 

Author Comment

by:emem
ID: 10974918
thanks guys ....i'll try this out ......madheeswar the agent still does not work it has to be done manually.  I was thinking if possible that every day when the database is opened it will prompt the user to run the agent .......thanks again
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.

 

Author Comment

by:emem
ID: 10974921
one more thing where will I post this code do i have to create a script library or just an action button to upload?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10974922
very strange...

Lets discuss this agent topic in that topic.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10974927
I placed this code in an agent and called the agent from an Action Button
@Command([ToolsRunMacro];"agentname")

0
 

Author Comment

by:emem
ID: 10975123
I see i'll work on that and get back to you ASAP
0
 

Author Comment

by:emem
ID: 10975280
The message finished was displayed but where can I view the data?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10975509
It will display in the views . And ur using some form to create documents using this code. So, give the selection formula in the view using this form and u can view all the docuemnts created by this agent.,
0
 

Author Comment

by:emem
ID: 10975883
I noticed that I  have to type the entire path of where the file is located can I just browse for the file in the directory?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10975900
You are free to change my code.

or place this code in a button and create a field on the form and browse for the file and the code should take the path and execute.
0
 

Author Comment

by:emem
ID: 10975979
oops! can you spare me a sample code? i'll increase your points ...my filename will be in this path and I will just browse for the file I want to upload ......please =)

C:\pdc\
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10976005
Create a field called path_x
and create a button and place this code*this code will take the path of file.
Sub Click(Source As Button)
      Dim workspace As New NotesUIWorkspace
      Dim files As Variant
      On Error Goto ErrorHandler
      files = workspace.OpenFileDialog(True, "File List", _
      "", "c:")
      Dim messagelist As String
      Forall filelist In files
      'messagelist = messagelist & filelist & Chr(10)
            messagelist = messagelist & filelist
      End Forall
      Dim uidoc As notesuidocument
      Set uidoc=workspace.currentdocument
      
      Call uidoc.fieldsettext("path_x",messagelist)
ErrorHandler:
      Resume Next
      'Messagebox messagelist
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10976007
and use this field as the path instead of having alert in my previous code.

0
 

Author Comment

by:emem
ID: 10976079
ok ok im so excited get back soon!!!
0
 

Author Comment

by:emem
ID: 10976179
I created a form created everything that you told me and inserted an action button in the form where the first code you gave me to upload but nothing is uploaded ....=( hmm where do you think I will put the uploading script in the form? thanks a million
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10976323
in hotspot button.
0
 

Author Comment

by:emem
ID: 10976360
I created this setup

created a form with the field path_x with 2 hotspot buttons browse(with the code you gave me to browse) upload (with this command @command([Toolsrunmacro];"Import") to call the agent we created.  But when I click on upload an error appeared when I debug it shows that the xlFileNames$ is blank it cannot hold the value of the filed path_x...did I miss something out? Do I have to declare path_x somewhere for it to be a global variable?

I change your original code

FROM =>xlFileName$ = Inputbox("What file name and path? example:H:\June282001.xls")

TO => xlFileName$ = path_x
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10976376
it should be:
dim ws as new notesuiworkspace
dim curdoc as notesdocument
dim curdoc=ws.currentdocument.document
xlFileName$ = curdoc.path_x (0)
0
 

Author Comment

by:emem
ID: 10976402
copied the code in the agent you created but got an error in the line

dim curdoc=ws.currentdocument.document
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 600 total points
ID: 10976412
Sorry, it should be:
set curdoc=ws.currentdocument.document

so ur a novice in Lotus Script?
0
 

Author Comment

by:emem
ID: 10976430
you can say that again my first time to handle scripting and to handle this kind of system PPHEW! thank GOD you are there =)
0
 

Author Comment

by:emem
ID: 10976463
it worked!!!!!! GREAT SCOTT you are a GURU in lotus ......i'll fix the display and get back to you on the agent THANKS!!!!!!! i'll accept this solution thanks thanks thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

598 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