Export to excel

HCL Domino Domino Forum
1

Hi can anyone help me i create an agent to export the document as well as add the document to excel it is working fine but the things is that for eg. in view if the marks is 2.66 then it is displaying fine but it is adding only 2 but not the .66 so i need all your help here is an agent

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim doc As NotesDocument

Dim uiview As NotesUIView

Dim ws As New NotesUIWorkspace

Dim collection As NotesDocumentCollection

Dim column As NotesViewColumn



Set db=session.currentDatabase

Print "Please Wait as the Excel Spreadsheet is being created..."





Set view=db.GetView("transfer")



Set doc=view.GetFirstDocument





Dim rownum As Integer,currentdocnum As Long

rownum = 4

currentdocnum=1



Dim xlapp,xlWork

Set xlapp = CreateObject( "Excel.Application" )

xlapp.Workbooks.add

Set xlWork = xlapp.ActiveWorkbook

xlapp.visible=True



xlWork.worksheets("Sheet1").cells(2, 5).value="Transfer to Excel"

xlWork.worksheets("Sheet1").cells(2, 5).Font.Size=14



xlWork.worksheets("Sheet1").cells(2, 5).Font.ColorIndex=10



xlWork.worksheets("Sheet1").cells(2, 5).Font.Bold = True





xlWork.worksheets("Sheet1").cells(3,1).font.bold = True

xlWork.worksheets("Sheet1").cells(3,1).columnwidth = 25

xlWork.worksheets("Sheet1").cells(3,1).value = "Name"



xlWork.worksheets("Sheet1").cells(3,2).font.bold = True

xlWork.worksheets("Sheet1").cells(3,2).columnwidth = 25

xlWork.worksheets("Sheet1").cells(3,2).value = "Phno"



xlWork.worksheets("Sheet1").cells(3,3).font.bold = True

xlWork.worksheets("Sheet1").cells(3,3).columnwidth = 25

xlWork.worksheets("Sheet1").cells(3,3).value = "Marks"



Dim b As Variant

Dim phtot As Long

Dim martot As Long

phtot = 0

martot = 0

While Not (doc Is Nothing)

	

	xlWork.worksheets("Sheet1").cells(rownum,1).value = doc.name(0)

	xlWork.worksheets("Sheet1").cells(rownum,2).value =doc.phno(0)

	xlWork.worksheets("Sheet1").cells(rownum,3).value =doc.marks(0)

	

	

	

	

	phtot = phtot + doc.phno(0)

	martot = martot + doc.marks(0)	

	

	

	

	Set doc=view.GetNextDocument(doc)

	

	rownum=rownum+1

Wend

xlWork.worksheets("Sheet1").cells(rownum,2).font.bold = True

xlWork.worksheets("Sheet1").cells(rownum,2).Font.ColorIndex=3

xlWork.worksheets("Sheet1").cells(rownum,3).font.bold = True

xlWork.worksheets("Sheet1").cells(rownum,3).Font.ColorIndex=3



xlWork.worksheets("Sheet1").cells(rownum,2).value = phtot

xlWork.worksheets("Sheet1").cells(rownum,3).value = martot

End Sub

2

Subject: export to excel

take

Dim phtot As Double

Dim marktot As Double

3

Subject: RE: export to excel

its not working

if the value is 5 and 5.1 then it is displaying 55.1 but i want there sum means 10.1 can anyone help me

4

Subject: RE: export to excel

Are the phno and marks fields number fields or have you stored their values as text? If they are not numbers then you may need to convert them to numbers. Try the following:

Dim phtot As Double

Dim martot As Double

phtot = 0

martot = 0

…

phtot = phtot + CDbl(doc.phno(0))

martot = martot + CDbl(doc.marks(0))

5

Subject: RE: export to excel

Thanks

6

Subject: export to excel

What do you mean by “…but it is adding only 2 but not the .66…”?When you debug it, does the value of martot change by 2.66 or by 2?

To me it looks like the target cell is formatted to cut the cyphers after the dot. You may set the target cells formatting to accept decimal values by using the range or application property “numberformat”.

Regards,

Manuel