Export to excel - center align

hcl-bot · March 25, 2009, 5:10am

Hi

I have written a export to excel code however I am not able to print Heading, Subheading and column heading in center alignment.

i tried .horozontalalignment = xlCenter

nothing is working. can any help me on this. below is my code.

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Dim xlSheet As Variant                   

Dim ws As New NotesUIWorkspace

Dim collection As NotesDocumentCollection



Set db=session.currentDatabase

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



Set collection = db.UnprocessedDocuments

Set doc=collection.GetFirstDocument



Dim rownum As Integer,currentdocnum As Long

rownum = 4

currentdocnum=1

sn = 1



Dim xlapp,xlWork

Set xlapp = CreateObject( "Excel.Application" )

xlapp.Workbooks.add

Set xlWork = xlapp.ActiveWorkbook

xlapp.visible=True



SheetCount =1                        

Set xlsheet = xlApp.workbooks(1).worksheets(SheetCount)                 



rows=1

With xlSheet

	.Cells(1,10)="UNAMID" 

End With



With xlsheet.cells.range("A1:J1") '---- Main Heading ---------	

	.Font.Bold=True

	.Font.Size=11

	.Font.Name="Arial"

	.WrapText = True 

	.VerticalAlignment = -4107

	.ColumnWidth = 18.71

	.MergeCells = True

	'.Interior.colorIndex = 55

	.font.colorIndex = 0

End With



rows=2

With xlSheet

	.Cells(2,10)="Residence Visa details" 

End With



With xlsheet.cells.range("A2:J2") '---- Sub Heading ---------

	.Font.Bold=True

	.Font.Size=9

	.Font.Name="Arial"

	.VerticalAlignment = -4107

	.ColumnWidth = 18.71

	.MergeCells = True

	.font.colorIndex = 0

End With



'xlWork.worksheets("Sheet1").cells(1, 5).value="UNAMID"

'xlWork.worksheets("Sheet1").cells(1, 10).Font.Size=12

'xlWork.worksheets("Sheet1").cells(1, 10).Font.ColorIndex=10

'xlWork.worksheets("Sheet1").cells(1, 10).Font.Bold = True



'xlWork.worksheets("Sheet1").cells(2,4).value="Residence Visa details"

'xlWork.worksheets("Sheet1").cells(2,10).Font.Size=10

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

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



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

xlWork.worksheets("Sheet1").cells(3,1).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,1).WrapText = True

xlWork.worksheets("Sheet1").cells(3,1).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,2).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,2).WrapText = True

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



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

xlWork.worksheets("Sheet1").cells(3,3).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,3).WrapText = True

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



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

xlWork.worksheets("Sheet1").cells(3,4).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,4).WrapText = True

xlWork.worksheets("Sheet1").cells(3,4).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,5).Font.Size=8

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

xlWork.worksheets("Sheet1").cells(3,5).value = "Received from S/M"

xlApp.ActiveSheet.Cells(3,5).WrapText = True

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



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

xlWork.worksheets("Sheet1").cells(3,6).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,6).WrapText = True

xlWork.worksheets("Sheet1").cells(3,6).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,7).Font.Size=8

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

xlWork.worksheets("Sheet1").cells(3,7).value = "Passport Number"

xlApp.ActiveSheet.Cells(3,7).WrapText = True

xlWork.worksheets("Sheet1").cells(3,7).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,8).Font.Size=8

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

xlWork.worksheets("Sheet1").cells(3,8).value = "Validity of the Passport"

xlApp.ActiveSheet.Cells(3,8).WrapText = True

xlWork.worksheets("Sheet1").cells(3,8).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,9).Font.Size=8

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

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

xlApp.ActiveSheet.Cells(3,9).WrapText = True

xlWork.worksheets("Sheet1").cells(3,9).Font.Bold = True



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

xlWork.worksheets("Sheet1").cells(3,10).Font.Size=8

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

xlWork.worksheets("Sheet1").cells(3,10).value = "Sent to KRT"

xlApp.ActiveSheet.Cells(3,10).WrapText = True

xlWork.worksheets("Sheet1").cells(3,10).Font.Bold = True



While Not (doc Is Nothing)

	If (doc.form(0) = "frmEntry") Then

		Goto last

	End If

	sName = doc.txtTitle(0) & " " & doc.txtFName(0) & " " & doc.txtLName(0)

	xlWork.worksheets("Sheet1").cells(rownum,1).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,1).value = sn

	

	xlWork.worksheets("Sheet1").cells(rownum,2).Font.Size=8

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

	xlApp.ActiveSheet.Cells(rownum,2).WrapText = True

	

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

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

	xlApp.ActiveSheet.Cells(rownum,3).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,4).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,4).value =doc.cmbNationality(0)

	xlApp.ActiveSheet.Cells(rownum,4).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,5).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,5).value =doc.dtRecvSM_1(0)

	xlApp.ActiveSheet.Cells(rownum,5).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,6).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,6).value =doc.cmbPassport_1(0)

	xlApp.ActiveSheet.Cells(rownum,6).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,7).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,7).value =doc.txtPassportNum_1(0)

	xlApp.ActiveSheet.Cells(rownum,7).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,8).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,8).value =doc.dtValidityPassport_1(0)

	xlApp.ActiveSheet.Cells(rownum,8).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,9).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,9).value =doc.cmbDesignation(0)

	xlApp.ActiveSheet.Cells(rownum,9).WrapText = True

	

	xlWork.worksheets("Sheet1").cells(rownum,10).Font.Size=8

	xlWork.worksheets("Sheet1").cells(rownum,10).value =doc.dtSentKRT1(0)

	xlApp.ActiveSheet.Cells(rownum,10).WrapText = True

	

	rownum=rownum+1

	sn = sn + 1

last :

	Set doc=collection.GetNextDocument(doc)

Wend

BorderColor = "A1:J" & sn+2

xlApp.Range(BorderColor).Cells.Borders.ColorIndex = 0

End Sub

Thanks,

Sanjay

hcl-bot · March 25, 2009, 5:27am

Subject: export to excel - center align

You can’t use Excel Constants in your LotusScript - you need to find the value of that constant. In the case of xlCenter, it seems to be -4108.

Try using that value instead of xlCenter.

hcl-bot · March 25, 2009, 6:22am

Subject: RE: export to excel - center align

Thanks a Lot DAVID it works

Sanjay