Formatting Excel File from VB .Net

[rmunson]

I am creating an Excel file from Visual Basic .Net. Sample code is below. I need to know how to align the cells horizontally. Any other formatting commands would also be helpful.

objExcel = CreateObject("excel.application")
objExcel.Visible = True
objExcel.Workbooks.Add()
objExcel.sheets(sheetnam).Cells(15, 12).Value = TotCopay
objExcel.sheets(sheetnam).Cells(15, 12).Font.Size = 8
objExcel.sheets(sheetnam).Cells(15, 12).Font.Bold = True
objExcel.sheets(sheetnam).Cells(15, 12).NumberFormat = "$##0.00"

 

[Sorwen]

In VS2k5 at least the commands are the same as if you did it in Excel so one of the best ways to find out information on this (if you don't already do VBA programming) is to have it record a macro while you do it and see what it does.

That said you should be able to do it like this:

objExcel.Sheets(sheetnam).Rows("1:2").RowHeight = 9

This would make row 1 and row 2 set to a Height of 9.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

I for got to mention that if you are going to do a lot of formatting and working with one sheet you might want to work with the sheet as an object directly. I can't remember where I got this (maybe even from here), but this is what I use now on most of mine.

objExcel = CreateObject("excel.application")
objBook = objExcel.Workbooks.Add
objSheet = ObjeBook.Worksheets(1)

objSheet.Cells(15, 12).Value = TotCopay
objSheet.Cells(15, 12).Font.Size = 8
objSheet.Cells(15, 12).Font.Bold = True
objSheet.Cells(15, 12).NumberFormat = "$##0.00"

It is really about preference though. This way it is a little easier to work with the sheet or even go back and work with the whole book if I have something that needs that.

-I hate Microsoft!
-Forever and always forward.

 

[rmunson]

I guess I didn't make my question totally clear. I'm wanting to align the text inside the cell. I tried objExcel.Sheets(sheetnam).Cells(15,12).HorizontalAlignment = Right, but it gives me an error on the "HorzontalAlignment" part of the command.

 

[earthandfire]

Because you have chosen the late binding approach, you don't have the benefit of intellisense.

Rather than trying to work out what constant name is required (in actual fact it is xlRight not Right - however, just using xlRight will not work by itself), use the Object Browser in Excel and find the constant value.

[tt]objExcel.Sheets(sheetnam).Cells(15,12).HorizontalAlignment = -4152[/tt]

You can find all the other constant values in the same way.

Alternatively rewrite your code to use early binding and take advantage of intellisense.


Hope this helps.

 

[rmunson]

I am not writing the program inside Excel. I am writing it in VB.Net. When I use the above command, I get an error on the "HorizontalAlignment.

 

[earthandfire]

It works perfectly for me!!

What else are you doing?

 

[Sorwen]

Yes I misunderstood. Like earthandfire said HorizontalAlignemnt is what works for me as well.

-I hate Microsoft!
-Forever and always forward.

 

[SiriusBlackOp]

To elaborate on earthandfire's late binding point... Late Binding (ie CreateObject) will really make your life difficult. I think most people here would Highly recommend that you use early binding like this:

Dim objExcel As New Excel.Application

This is pretty cool and might really help you out. The examples are given in C# instead of VB.NET, but if you don't know C# at all I've also provided a link to a pretty decent conversion tool.

http://blogs.msdn.com/carlosag/archive/2005/08/27/WhyIWroteExcelXmlWriter.aspx

Convert C# to and from VB.NET:

http://authors.aspalliance.com/aldotnet/examples/translate.aspx

Senior Software Developer

http://www.scoutsft.com

 

[RonRepp]

As EandA said, early binding is the way to go. This came from a long discussion in thread222-1361788.

As far as using the horizontal positioning in .Net, you must first use the interop for Excel in order to get the correct argument.

Something like this:

Dim XL as Excel.Application
XL=New Excel.Application
With XL
   .ActiveCell.HorizontalAlignment = [COLOR=blue][b]Excel.Constants.xlRight [/b][/color] '(or -4152 as EandA said)
End With

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[earthandfire]

... not forgetting to add the reference to Excel (from the COM tab).

 

[RonRepp]

True...I assume too many things.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.