emem
asked on
Importing Excel Data using Lotus Notes
does anybody have a script/ formula in uploading excel data using lotus notes?
You can also search this forum, you know, this has been asked at least a 1000 times before ....
cheers,
Tom
cheers,
Tom
ASKER
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
ASKER
one more thing where will I post this code do i have to create a script library or just an action button to upload?
very strange...
Lets discuss this agent topic in that topic.
Lets discuss this agent topic in that topic.
I placed this code in an agent and called the agent from an Action Button
@Command([ToolsRunMacro];" agentname" )
@Command([ToolsRunMacro];"
ASKER
I see i'll work on that and get back to you ASAP
ASKER
The message finished was displayed but where can I view the data?
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.,
ASKER
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?
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.
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.
ASKER
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\
C:\pdc\
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(T rue, "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.currentdoc ument
Call uidoc.fieldsettext("path_x ",messagel ist)
ErrorHandler:
Resume Next
'Messagebox messagelist
End Sub
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(T
"", "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.currentdoc
Call uidoc.fieldsettext("path_x
ErrorHandler:
Resume Next
'Messagebox messagelist
End Sub
and use this field as the path instead of having alert in my previous code.
ASKER
ok ok im so excited get back soon!!!
ASKER
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
in hotspot button.
ASKER
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
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];"
I change your original code
FROM =>xlFileName$ = Inputbox("What file name and path? example:H:\June282001.xls"
TO => xlFileName$ = path_x
it should be:
dim ws as new notesuiworkspace
dim curdoc as notesdocument
dim curdoc=ws.currentdocument. document
xlFileName$ = curdoc.path_x (0)
dim ws as new notesuiworkspace
dim curdoc as notesdocument
dim curdoc=ws.currentdocument.
xlFileName$ = curdoc.path_x (0)
ASKER
copied the code in the agent you created but got an error in the line
dim curdoc=ws.currentdocument. document
dim curdoc=ws.currentdocument.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you can say that again my first time to handle scripting and to handle this kind of system PPHEW! thank GOD you are there =)
ASKER
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
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.