Formating Cells In Excel From VB

[softweng]

I am creating an Excel Spreadsheet through my VB Code. I want to know how I can center the text
In a cell. I can make it Bold and the correct Size, but I also want to Justify the text to
the center of the cell instead of using Left justification. Does anyone know how to do this?
I have listed my code below, Please let me know if there is a way to do this. Thanks!!!

private Sub cmdCreateExcl_Click()
Dim ExclApp as Excel.Application
Dim WorkBook as Excel.WorkBook
Dim WorkSheet as Excel.WorkSheet
Dim path as string
Dim FileExt as string
Dim i as Integer

on error GoTo ErrHandler

'//Ask User for Path And Name Of SpreadSheet
path = InputBox("Enter The Path And Name Of The SpreadSheet to Create.", "Create SpreadSheet", "C:\")

'//Create SpreadSheet If A Path Was Entered
If path <> "" then
'//If SpreadSheet Exists Already then Delete It
FileExt = Right(path, 4)
If FileExt = ".xls" then
If Dir(path) <> "" then Kill path
else
If Dir(path & ".xls") <> "" then Kill (path & ".xls")
End If
DoEvents

'//Creat Instance Of Excel
set ExclApp = CreateObject("Excel.Application")

'//Don't Show Excel Application
ExclApp.Visible = false

'//Turn Off Excel Dialog Alerts
ExclApp.DisplayAlerts = false

'//Create The WorkBook
set WorkBook = ExclApp.Workbooks.Add

'//Create The WorkSheet
set WorkSheet = ExclApp.Worksheets.Add

'//Name The WorkSheet
WorkSheet.Name = "InspResults"

'//set Column Width
WorkSheet.Columns("A:H").ColumnWidth = 20
WorkSheet.Columns("I:K").ColumnWidth = 15

'//set Column Headings
WorkSheet.Cells(1, 1).Value = "Data1"
WorkSheet.Cells(1, 2).Value = "Data2"
WorkSheet.Cells(1, 3).Value = "Data3"
WorkSheet.Cells(1, 4).Value = "Data4"
WorkSheet.Cells(1, 5).Value = "Data5"
WorkSheet.Cells(1, 6).Value = "Data6"
WorkSheet.Cells(1, 7).Value = "Data7"
WorkSheet.Cells(1, 8).Value = "Data8"
WorkSheet.Cells(1, 9).Value = "InspStatus"
WorkSheet.Cells(1, 10).Value = "date"
WorkSheet.Cells(1, 11).Value = "time"

'//Format Column Headings
i = 0
for i = 1 to 11
WorkSheet.Cells(1, i).Font.Size = 12
WorkSheet.Cells(1, i).Font.Bold = true
next i

'//Delete The Unused Worksheets
i = 0
for i = 1 to 3
WorkBook.Worksheets("Sheet" & i).Delete
next i

'//Save The Workbook
If FileExt = ".xls" then
ExclApp.ActiveWorkbook.SaveAs (path)
else
ExclApp.ActiveWorkbook.SaveAs (path & ".xls")
End If
DoEvents

'//Close The Workbook
ExclApp.Workbooks.Close

'//set Objects to nothing to Prevent Memory leaks
set WorkSheet = nothing
set WorkBook = nothing
set ExclApp = nothing
End If

'//Store The new Database Path In The Text Box
If FileExt = ".xls" then
txtExclPath.Text = path
else
txtExclPath.Text = path & ".xls"
End If
txtExclPath.Refresh

Exit Sub

ErrHandler:
'//Close The Workbook
ExclApp.Workbooks.Close

'//set Objects to nothing to Prevent Memory leaks
set WorkSheet = nothing
set WorkBook = nothing
set ExclApp = nothing

ProcessError ("frmExcel.cmdCreateExcl_Click")

End Sub

[Kdev]

xls.Cells(1, 1).HorizontalAlignment = xlHAlignCenter

The msdn library has all of this information under the Microsoft Excel/Visual Basic Language Reference.

-K

[softweng]

Thanks It Worked Great!!!!!
I tried finding information in MSDN but couldn't
find anything on formating from VB.
Thanks Again!

[Kdev]

I know what you mean. the MSDN is so complex that it takes me some time to find new information there. Here is a link:

http://msdn.microsoft.com/library/officedev/off2000/xlmscListOfWorksheetFunctions.htm

I'm not sure how to copy a link to an expanded TOC but if you follow the above link and hit Show TOC then you should see the correct path where all the VB info is for Excel 2000.

From the root msdn it is: Office Developer Documentation -> Office 2000 Documentation -> Microsoft Office 2000 Language Reference -> Microsoft Excel 2000 Reference -> Microsoft Excel Visual Basic Reference

Whew! All in 1 breath :-)

-K

[softweng]

Thanks For The Link!!!
MSDN is a great tool but sometimes it is a real
pain to find the information your looking for.
I appreciate your help!!!