Import data from excel to notes in Web

HCL Domino Domino Forum
1

Hi All

Actually i have develop code for importing data from excel to notes in lotus script and this is working fine through Lotus Client.But problem is in web coz Agent run on Server and my Excel file is locate on Local that’s why unable to find file to import through Server.

Code is

Public Class DataUpload

frmname As String

filepath As String

startrow  As Integer

endrow  As Integer

row As Integer

count As Integer



Sub New (fpath As String)

	filepath=fpath

End Sub



Sub Delete

	Print "Deleting text object."

End Sub



Public Sub Setdata(ffname As String, Srow As Integer,Erow As Integer,fieldsCount As Integer) 		

	count=fieldsCount			

	startrow=Srow		

	endrow=Erow	

	frmname= ffname

	row=Srow		

End Sub



Public Sub Importdocument()		

	If   filepath = ""  Then

		Messagebox "Please give  path of excel file "  , MB_OK + MBICONINFORMATION, "Warning"

		Exit Sub

	Else

		xlFilename = filepath

	End If

	

	Dim Excel As Variant

	Dim xlWorkbook As Variant

	Dim xlSheet As Variant	

	

	Set Excel = CreateObject( "Excel.Application" ) 		

	Excel.Workbooks.Open xlFilename

	Set xlWorkbook = Excel.ActiveWorkbook

	Set xlSheet = xlWorkbook.ActiveSheet	

	

	Redim temp(0 To count-1) As String

	While row <=endrow	

		With xlSheet					

			For i=0 To count-1

				temp(i)=Trim$(.Cells( row, i+1 ).Value)     			

			Next	

			Call Createdocument(frmname,temp,count)

			row = row+1          

		End With

	Wend 

	

	Print "Disconnecting from Excel..."		

	xlWorkbook.Close False	

	Excel.Quit 	

	Set Excel = Nothing 	

	Exit Sub

Error_call:

	Messagebox "Check SW Version or Excel file location or invalid datatype" ,MB_OK+MB_ICONINFORMATION,"Warning ! "

	Exit Sub		

End Sub

End Class

Sub Createdocument(frmname As String,temp() As String,count As Integer)

Dim session As New NotesSession	

Dim db As NotesDatabase

Dim docImport As NotesDocument

Set db = session.CurrentDatabase	

Dim strArray() As String 

Dim item As NotesItem		



Set docImport= New NotesDocument(db)	

Redim strArray(0 To count-1) As String

For i=0 To count-1

	strArray(i)=temp(i)			

Next

docImport.Form = frmname						

Select Case frmname

Case "frmTry"

	docImport.Ename=strArray(0)

	docImport.Eaddress=strArray(1)

	docImport.Emobile=strArray(2)		

	Stop		

Case "Emp"

	

	docImport.Emp_ID = strArray(0) 

	docImport.Emp_fname = strArray(1) 

	docImport.Emp_lname= strArray(2) 

	docImport.empcat= strArray(3)

	docImport.EmpDOB= strArray(4)

	docImport.Emp_DOB= strArray(4)			

	docImport.Emp_age=Evaluate({@Year(@Now)-@Year(@Date(@TextToTime(Emp_DOB) ) ) }, docImport)

	docImport.Anv_Date= strArray(5)

	docImport.Empmail= strArray(6)

	docImport.per_id= strArray(7)

	docImport.Emp_paddr= strArray(8)

	docImport.city= strArray(9)

	docImport.PinCode= strArray(10)

	docImport.Mob_No= strArray(11)

	docImport.Tel_No= strArray(12)

	docImport.Join_Date= strArray(13)

	docImport.Location="Gurgaon"

	docImport.Emp_con= strArray(14)

	docImport.Leav_Date= strArray(15)

	docImport.Empdep= strArray(16)

	docImport.Empidesg= strArray(17)			

	docImport.EmpNewDesg= strArray(18)

	docImport.Empphext= strArray(19)

	docImport.PAN_No= strArray(20)

	docImport.Pass_No= strArray(21)

	docImport.PF_No= strArray(22)

	docImport.ICIC_No= strArray(23)

	docImport.emrp= strArray(24)

	docImport.Qualification1= strArray(25)

	docImport.Exp_Bfr_PKC= strArray(26)

Case "DepartmentMaster"

	docImport.newDepart=strArray(0)

	docImport.hod=strArray(1)

Case "DepartmentMaster"

	docImport.Desigmaster=strArray(0)

	docImport.desigdep=strArray(1)

	docImport.Roledes=strArray(1)

Case "CategoryMaster"

	docImport.Newcategry=strArray(0)

	docImport.Categdescrip=strArray(1)

End Select

Call docImport.save(False,False)

End Sub

This code is working on client but in web when i use File Upload Control unable to find the file path,Plz help me.

Regards Parul

2

Subject: import data from excel to notes in Web

HI Manu as u told me i followed those steps and now it is working,the steps are->make a richtext field named Body,after saving the document with file uplaod control(having Attachment) ,the field Body gets its handle.

Now extract on server and the normal Upload. :slight_smile:

Thanks for Quick Response

Regards Parul

3

Subject: import data from excel to notes in Web

You helped me figure out how to import from the web with your code. Here is how you fix the path portion on the web:

Create a file upload field on a form to be used on the web.

Your querysave agent will have a code like this:

Forall o In doc.Items

If Trim(o.name) = “$FILE” Then

Set object=doc.GetAttachment(o.values(0))

uname=Lcase(object.name)

'you need a directory created in a specific drive

Call object.extractfile("f:\jdvp" & object.name)

fn=“f:\jdvp\” & object.name

End If

End Forall

now use fn as your file name in the command below:

Excel.Workbooks.Open fn

Works great!

4

Subject: RE: import data from excel to notes in Web

I tried but I got the error below.“Cannot create automation object”

Could you help me ?

Thanks

5

Subject: import data from excel to notes in Web

Hi!

You will have to save attachment in your document and take your excel at server.There detach it and then process it at server.

Manu