VBA Excel: formatting cells 1

[Davide77]

Hallo,
Does someone know where I can find a complete list of the keywords and syntax used in vba in order to format excel cells, such as:

.Columns("A&quot.ColumnWidth = 50
.Cells(1, 1).Interior.ColorIndex = "15"
.Rows("1&quot.RowHeight = 36
.Range("A1:F1&quot.Font.Bold = True
.Range("G1:I1&quot.Merge

how to change the cell formatting(time, text, date, etc), how to set the vertical and horizontal alignment, etc

 

[xlbo]

What you have asked for would extend to many, many pages - a better idea may be to record yourself performing the actions in excel.

Tools>Macros>Record New Macro

You would also need a grasp of the excel object model ie knowing that columnwidth is a property of the columns collection which can be referenced from the range object OR the worksheet object

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Davide77]

Thanks xlbo,
to use the macros is a good idea. I cannot find out the alignment thing, if I write:

.Rows("2&quot.VerticalAlignment = xlCenter

as I saw in few sources the debugger tells me there is a variable not defined: "xlcenter".
Any idea?

 

[xlbo]

unfortunately not - that is a valid constant and exactly what I get if I record setting the vertical alignment...

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Loomah]

Hi
There's one of those really useful comments in the help file the reads

"Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed."

Dunno what it's supposed to mean!!!

You could try using
xlVAlignCenter
to see if it makes a difference. Both this and xlCenter worked for me.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Davide77]

For me neither one nor the other worked. I'm actually working on an Excel sheet, but the module starts from an access form. Don't know if this can make any difference.

 

[Loomah]

Hi
Running things from Access forms is a litle beyond me but you might want to post a little more of the code you have. Do you have relevant references to Excel set up etc etc?
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Davide77]

hi loomah,
I'm taking some data from an access query and then send it to excel and then (trying) to format the cells. The code is here:

Sub Export_To_Excel()

Dim strSql As String
Dim dBase As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim uRow As Integer
Dim xlApp As Object
Dim Sht As Object
Dim RecordQuantity As String


DoCmd.SetWarnings False
'setup query to get all records from a table
strSql = "SELECT * FROM [Qry_Room];"
RecordQuantity = DCount("[Room]", "qry_Room") + 3

Set dBase = CurrentDb()
Set rs = dBase.OpenRecordset(strSql, dbOpenDynaset)
Set rs2 = dBase.OpenRecordset(strSQL2, dbOpenDynaset)
'Create Excel object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
Set Sht = xlApp.ActiveWorkbook.Sheets(1)
uRow = 4


' Loop through the records and copy them to worksheet.
Do Until rs.EOF

For i = 0 To rs.Fields.Count - 1
Sht.Cells(uRow, i + 1).Value = rs(i)
Next i
rs.MoveNext
uRow = uRow + 1

Loop





'Here's where the formatting happens
With Sht
.Name = "Give the sheet a name"
'set cell format to general
.Range("A1:A200").NumberFormat = "@"
.Columns("A").Autofit
.Rows("4:" & RecordQuantity).RowHeight = 36
' 'make titles bold
.Range("A1:F1").Font.Bold = True
.Range("G1:I1").Merge

' alignment
' .Rows("2").HorizontalAlignment = xlVAlignCenter
'.Rows("4:" & RecordQuantity).VerticalAlignment = xlVAlignCenter
.Columns("B").NumberFormat = "hh:mm"
.Columns("C").NumberFormat = "hh:mm"
.Columns("F").NumberFormat = "dd:mm:yyyy"
.Columns("B").Autofit
.Columns("C").Autofit
.Cells.Interior.ColorIndex = "2"
.Cells(1, 1).Interior.ColorIndex = "15"

End With
Sht.SaveAs "C:\uffa.xls"

Set Sht = Nothing
xlApp.Quit
Set xlApp = Nothing
Set rs = Nothing
Set dBase = Nothing
End Sub

 

[xlbo]

Fortunately, constants are just that. You can just use the number they represent. In this case

1 = Top
2 = Center
3 = Bottom
4 = Justified
5 = Distributed

so .Rows("4:" & RecordQuantity).VerticalAlignment = 2
should work

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Loomah]

xlbo
Thanks for that, I couldn't find them!! I was about to ask where are Left and Right but they wouldn't really apply to VerticalAlighnment would they!?!?

David
I don't know what the problem is. Although, as I say, I don't know Access too well I follow you code and nothing seems out of place. Have you tried running the formatting codes in Excel?

I'm now wondering if this does have something to do with the Remark in the help file I referred to.

Hopefully using the constant values works.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Loomah - I couldn't find 'em either - got 'em by trial and error

Most constants use either

-1,0,1
or 1,2,3......

so I guessed and started with a 1 - went to the cell and saw that it was top aligned so just carried on putting the numbers in and checking the alignment of the cell I was testing on

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Loomah]

Ah yes, the famous "hit it till it works" method I've favoured for many years!

Interesting if you run this code on formatted cells
MsgBox ActiveSheet.Rows(5).VerticalAlignment

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

Anyway, I guess the Excel object model wasn't referenced in the Access VBA project.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Davide77]

thanks xlbo

 

[ggriffit]

There is a constant list at

http://www.geocities.com/ResearchTriangle/9834/msexcel8.htm

also worth a look is

http://www.greggriffiths.org/webdev/both/excel/