[RESOLVED] Programmatically Centered Text In Excel Spreadsheet

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

VB Code:

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

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

VB Code:

'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

:)

Quote:

---

Originally Posted by RobDog888

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

VB Code:

'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?

VB Code:

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

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. :D

.

Quote:

---

Originally Posted by RobDog888

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

---

Good point. :blush: (Force of habit :D )

Quote:

---

Originally Posted by RobDog888

[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?

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

VB Code:

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 .

Quote:

---

Originally Posted by RobDog888

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

VB Code:

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! :D

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

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. ;)

Quote:

---

Originally Posted by RobDog888

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.

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. ;)

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

VB Code:

.HorizontalAlignment = xlCenter

What is Selection?

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

VB Code:

.PaperSize = 8

stands for A3 paper format.

Kind regards,
J

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

What version are you running?

Quote:

---

Originally Posted by RobDog888

What version are you running?

---

Excel 2003

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

Quote:

---

Originally Posted by RobDog888

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.

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

VB Code:

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

Quote:

---

Originally Posted by RobDog888

[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

Quote:

---

Originally Posted by RobDog888

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

VB Code:

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. :rolleyes:

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

VB Code:

'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

Quote:

---

Originally Posted by RobDog888

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

VB Code:

'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??? :mad:

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

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

VB Code:

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

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

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. :D

When I use this

VB Code:

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

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

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.

Quote:

---

Originally Posted by 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.

---

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

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

VB Code:

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

Quote:

---

Originally Posted by RobDog888

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

VB Code:

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

---

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

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. :thumb:

Cool, glad it finally worked out. :thumb: