Export to excel

HCL Domino Domino Forum
1

Hi all can anyone help me for doing this.The thing is that i want the date should be display as like month wise January,Feb,March but it is displaying the date for eg 14/1/2009 i dont know how to do this.For eg if mm=1 then january i want if mm=2 then feb

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("Approved")

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 = 10

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



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

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

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



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

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

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



Dim b As Variant

Dim martot As Double



martot = 0





While Not (doc Is Nothing)

	

	

	

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

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

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

	

	

	

	martot = martot + Cdbl(doc.leave(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,3).value = martot

End Sub

2

Subject: Export to excel

You can just use the numberformat property.

try this:

xlWork.worksheets(“Sheet1”).cells(rownum,2).numberformat=“MMMM”

OR you can use format$ to just put the month name in there e.g.

xlWork.worksheets(“Sheet1”).cells(rownum,2).value =format$(doc.startdate(0),“MMMM”)

Which one you choose will depend on whether you need to use elements of the date later on.

Number formats for dates can be any string containing combinations of dd mm yy hh mm ss for the different date-time components. The cell will contain the actual date but the user will see the numberformat. If you are using Excel this is set usignt he format cell menu option - you can see a list of numberformats there or record a macro of you formatting the cell as you want it to appear and then view the macro code to see the numberformat string.

Hint: if you get any issues it may be because Excel sometimes thinks that dates are in US date format i.e. mm/dd/yyyy

If you are getting the wrong month or an error, then do this FIRST

xlWork.worksheets(“Sheet1”).cells(rownum,2).value =format$(doc.startdate(0),“MM/dd/yyyy”)

and then use numberformat to display the month.

HTH

3

Subject: RE: Export to excel

Hi i tried but still facing problem it is exporting but the date for eg. 2/12/2009 not Feb for this i writ a code help me any one

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase	

Dim uidoc As NotesUIDocument

Dim ws As New NotesUIWorkspace

Dim doc As NotesDocument

Dim view As NotesView

Dim n As Integer

Dim num  As Variant



Set db=session.currentdatabase



Set uidoc=ws.currentDocument



Set view = db.GetView("vwTrialSRLNoLD1")



trialno = "xxx"

mmm = "xx"

plant = "LD1"

Set doc = view.getFirstDocument

If Not doc Is Nothing Then

	num = doc.tno(0)

	yy = Right(Year(Date),4)

	mmx = Month(Now)

	Rset mmm = mmx

	If mmm=01 Then mon="Jan"

	If mmm=02 Then mon="Feb"

	If mmm=03 Then mon="Mar"

	If mmm=04 Then mon="April"

	If mmm=05Then mon="May"

	If mmm=06Then mon="June"

	If mmm=07Then mon="July"

	If mmm=08Then mon="Aug"

	If mmm=09Then mon="Sept"

	If mmm=10Then mon="Oct"

	If mmm=11Then mon="Nov"

	If mmm=12Then mon="Dec"

	

	mm = mon

	Rset trialno = num

	

	sno$ = plant & "/" & yy & "/"  &  mm & "/" & Cstr(trialno)

	

	

	Call uidoc.FieldSetText("trialno",sno$)

	doc.tno = (num +1)

	Call doc.save(True,True)  

	Call view.refresh

	

End If

End Sub