[RESOLVED] Programmatically Centered Text In Excel Spreadsheet

[Hack]

I'm programmtically creating a brand new excel spreadsheet, in which I'm dumping the contents of a 5 column ListView control.

When creating Excel Templates, I can pre-center whatever cells I need to, but this spreadsheet is being created on the fly and I need to know how to center specific cells while I'm creating it.

The ListView will go into Columns A, B, C, D, and E and I need Column A, C and & D to be centered. How would I do that? Here is my Sub

Private Sub DisplayReport(pstrLocation As String, pRecordSet As ADODB.Recordset, _
            Optional pblnSendToFile As Boolean, Optional pblnSendToPrinter As Boolean)
            
Dim lvwItem As ListItem
Dim objExcel As Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim i As Integer
Dim j As Integer
lvwAR.ListItems.Clear
Do While Not pRecordSet.EOF
    Set lvwItem = lvwAR.ListItems.Add(, , pRecordSet.Fields.Item("prov_cd").Value)
    lvwItem.SubItems(1) = pRecordSet.Fields.Item("prov_nm").Value
    lvwItem.SubItems(2) = pRecordSet.Fields.Item("prov_fy_end_dt").Value
    lvwItem.SubItems(3) = pRecordSet.Fields.Item("amend_cd").Value
    lvwItem.SubItems(4) = pRecordSet.Fields.Item("amend_cat_cd").Value
    pRecordSet.MoveNext
   Loop
 
If pstrLocation = "screen" Then
   frmShowAllAR.Height = 7935
   frmShowAllAR.Top = 690
Else
    Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    With lvwAR
        For i = 1 To .ColumnHeaders.Count
            shWorkSheet.Cells(1, Chr(64 + i)) = .ColumnHeaders(i)
        Next
'=====> here is where Im dumping the listview to excel
'=====>i need columns A, C & D centered somewhere somehow in this section
        For i = 1 To .ListItems.Count
            shWorkSheet.Cells(i + 2, "A") = .ListItems(i).Text
            For j = 2 To .ColumnHeaders.Count
                shWorkSheet.Cells(i + 2, Chr(64 + j)) = .ListItems(i).SubItems(j - 1)
            Next
        Next
        shWorkSheet.Columns("A:BZ").AutoFit
        If pblnSendToFile = True Then
           'if it already exists kill the sucker
           If Dir$(App.Path & "\AllAppealsReopens.xls") <> vbNullString Then
              Kill App.Path & "\AllAppealsReopens.xls"
           End If
             bkWorkBook.SaveAs FileName:=App.Path & "AllAppealsReopens.xls"
             objExcel.Visible = False
             Exit Sub
        End If
        If pblnSendToPrinter = True Then
           bkWorkBook.PrintOut , , Copies:=1
           objExcel.Visible = False
           Exit Sub
        End If
    End With
    objExcel.Visible = True
End If
End Sub

[RobDog888]

Instead of centering each cell, how about centering the entire column at once? If not just change the .Columns to the desired Range.

'For a single cell...
'Workbooks(1).Sheets(1).Range("E3").Select
'For a entire column...
Workbooks(1).Sheets(1).Columns("E:E").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

[Hack]

Instead of centering each cell, how about centering the entire column at once? If not just change the .Columns to the desired Range.

'For a single cell...
'Workbooks(1).Sheets(1).Range("E3").Select
'For a entire column...
Workbooks(1).Sheets(1).Columns("E:E").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

Since I need Column A, I'm guessing I would need to put this whole thing within my F i = Loop, and since C and D are contained within the For j = loop, I would also need to put that whole thing there are well?

For i = 1 To .ListItems.Count
            shWorkSheet.Cells(i + 2, "A") = .ListItems(i).Text
            For j = 2 To .ColumnHeaders.Count
                shWorkSheet.Cells(i + 2, Chr(64 + j)) = .ListItems(i).SubItems(j - 1)
            Next
        Next

[RobDog888]

No, if you want columns formatted with centered text it should just be ok with the call before the loop where the cell contents get added.

Ps, tsk, tsk, shouldnt this be in the OD forum.

[Hack]

.

Ps, tsk, tsk, shouldnt this be in the OD forum.

Good point. (Force of habit )

[color=navy]No, if you want columns formatted with centered text it should just be ok with the call before the loop where the cell contents get added

Ok, but I don't want them all centered. ONLY A, C and D

I do not want B or E centered. So, I would still use it before the loop where the cell contents get added?

[RobDog888]

Ok, then A will be a single call and C and D will be a joined call.

Workbooks(1).Sheets(1).Columns("A:A").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
 
Workbooks(1).Sheets(1).Columns("C:D").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

Yes, before the part where your entering the text as the formatting will retian unless your doing a PasteSpecial which will change the formatting to what the source range was formatted as .

[Hack]

Ok, then A will be a single call and C and D will be a joined call.

Workbooks(1).Sheets(1).Columns("A:A").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
 
Workbooks(1).Sheets(1).Columns("C:D").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

Yes, before the part where your entering the text as the formatting will retian unless your doing a PasteSpecial which will change the formatting to what the source range was formatted as .

Nope, no PasteSpecials in this one. I'll give 'er a shot!

Do I actually need the enter With/End With or will just the HorizontalAlignment do it for me?

[RobDog888]

If its a new workbook and you are not needing to change the default formatting in those other properties then no.

What I do is a format of a workbook upon it opening or creation so everything will be in place before any data is added or updated. Same with sizing of the column widths, font styles etc. too.

It formats faster if there is no data in the sheet.

[Hack]

What I do is a format of a workbook upon it opening or creation so everything will be in place before any data is added or updated. Same with sizing of the column widths, font styles etc. too.

Ah....this is a good idea, and one I shall employ.

[RobDog888]

Also, depending on your needs, you can set the sheet as hidden, format it, and then show the sheet if I was adding a new sheet to an existing opened workbook. You get the idea.

[Hack]

I get a "Method or Data member not found" on this line

.HorizontalAlignment = xlCenter

What is Selection?

[Killazzz]

Hello,

I once had a similar problem and sometimes the constant names aren't resolved correctly.

You then need to use the value for example

.PaperSize = 8

stands for A3 paper format.

Kind regards,
J

[RobDog888]

Selection is the selected object, in this case it will be the column.

What version are you running?

[Hack]

What version are you running?

Excel 2003

[RobDog888]

Then you should have HorizontalAlignment available to you. Did you qualify it if you took out the with block?

[Hack]

Then you should have HorizontalAlignment available to you. Did you qualify it if you took out the with block?

Now that you mention it, none of the selections under With Selection appear in intellisense.

[RobDog888]

Then you probably have an error somewhere that is causing the intellisense not to function.

Also, you shouldnt use the New in the dimensioning in theis line

Dim objExcel As [b]New[/b] Excel.Application
 
'Should do like...
Dim objExcel As Excel.Application
 
Set objExcel = New Excel.Application

[Hack]

[color=navy]Then you probably have an error somewhere that is causing the intellisense not to function.

Any ideas where that error could be? The Sub and all of its wonders works just fine. I just wanted a little formatting done

Also, you shouldnt use the New in the dimensioning in theis line

Dim objExcel As [b]New[/b] Excel.Application
 
'Should do like...
Dim objExcel As Excel.Application
 
Set objExcel = New Excel.Application

Yes, I know and I have a nasty habit of doing that. It is funny. I can catch things like this when I'm looking at other peoples code, you would think I would be able to catch it when looking at mine.

[RobDog888]

Ok, eliminating a few possibilities here and not doing any bashing

'Should reference excel here...
Dim bkWorkBook As [b]Excel.[/b]Workbook
Dim shWorkSheet As [b]Excel.[/b]Worksheet
 
'Your creating another instance here...
Else
    Set objExcel = New Excel.Application
 
'You can also do like this...
Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter

[Hack]

Ok, eliminating a few possibilities here and not doing any bashing

'Should reference excel here...
Dim bkWorkBook As [b]Excel.[/b]Workbook
Dim shWorkSheet As [b]Excel.[/b]Worksheet
 
'Your creating another instance here...
Else
    Set objExcel = New Excel.Application
 
'You can also do like this...
Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter

Are you insulting my code mister???

Thats it! I'm reporting you to a Mod (providing I can find one of the lazy SOBs )

I like short and sweet. I'm going to give this a whirl

Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter

[RobDog888]

Thats it! You insulted me for the last time. I'm reporting your post first HA HA That will show you whos the Guru around here.

The short and sweet code is better if its just a few properties you need to set but when you need all of them its a pain and why I lke the with block. Still wonder why the intellisense isnt working for you.

Oh, do a reboot and that should solve it.

[Hack]

When I use this

Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter

on either the workbook or worksheet object I get an error message of:

[RobDog888]

Make sure your running the latest updates for Office. I am running Excel 2003 SP1. See if you can find it in the Object Browser too.

[Hack]

Make sure your running the latest updates for Office. I am running Excel 2003 SP1. See if you can find it in the Object Browser too.

I, too, am running Excel 2003 SP1 and am using the Microsoft Excel 11.0 Object Library.

[RobDog888]

Did you change the objects to the ones your using in your code?

Workbooks(1).Sheets(1)...
objExcel.Workbooks(1).Sheets(1)...

[Hack]

Did you change the objects to the ones your using in your code?

Workbooks(1).Sheets(1)...
objExcel.Workbooks(1).Sheets(1)...

Yes, but I think I might have changed the wrong ones. Hold on.

[Hack]

Yep, that was it.

I was using the Workbook/Worksheet object, not the Excel Object. When I used the Excel Object, everything worked just fine.

[RobDog888]

Cool, glad it finally worked out.