[RESOLVED] Cell Formatting in an Excel Object

[guyjasper]

hi, does anyone knows how to change the cell formatting of a cell in an excel object? like change the font, background color, etc ...

can this be done?

hope you guys can help.

[dee-u]

Have a look at this, I think I've got it from this forum and modified it to suit my needs...

Public Sub ExportRstToExcel(ByVal Rst As ADODB.Recordset, ByVal sName As String)
        
    Dim i           As Long
    Dim vLimit      As Long
    Dim vCount1     As Long
    Dim vCount2     As Long
    Dim exl         As Object
    Dim wkb         As Object
    Dim rng         As Object
        
    'Use late-binding so that the app
    'is not tied to a specific version of Excel;
    'drawback is there is no intellisense and speed is slower
    
    'Set exl = New Excel.Application --> Binded version
    
    Set exl = CreateObject("Excel.Application") '--> initialize
    'create workbook object
    Set wkb = exl.Workbooks.Add
    'set active worksheet
    wkb.ActiveSheet.Name = sName
            
    With Rst
        'lblStatus.Caption = "Setting columns..."
        vLimit = .Fields.Count - 1
        'set column headers
        For i = 0 To vLimit
            wkb.ActiveSheet.Cells(1, i + 1).Font.Bold = True
            wkb.ActiveSheet.Cells(1, i + 1).Font.Color = vbBlue
            wkb.ActiveSheet.Cells(1, i + 1) = .Fields(i).Name
        Next i
        'lblStatus.Caption = "Loading Records..."
        
        'load records
        wkb.ActiveSheet.Cells(2, 1).CopyFromRecordset Rst
        vCount1 = Rst.RecordCount + 2
        
        'set background color for the top row (headers)
        Set rng = exl.Range(exl.Cells(1, 1), exl.Cells(1, .Fields.Count))
        rng.Select
        With exl.Selection.Interior
            .ColorIndex = 6 'yellow
            .Pattern = 1    'xlSolid
        End With
        
        'autofit all values
        exl.Range(exl.Cells(1, 1), exl.Cells(i, .Fields.Count + 1)).Select
        exl.Selection.Columns.AutoFit
        exl.Cells(2, 1).Select
    End With
    
    'freeze first row
    '-------------------------------------------------------------------
    'cell("A2") is already selected so we may freeze row right above it
    '-------------------------------------------------------------------
    exl.ActiveWindow.FreezePanes = True
    
    exl.Visible = True
 
    'clean up and exit
    Set exl = Nothing
    Set wkb = Nothing
    Set rng = Nothing
    Rst.Close
    Set Rst = Nothing
End Sub

[Liquid Metal]

    With Range("A1").Interior
        .ColorIndex = 41
        .Pattern = xlSolid
    End With

[RobDog888]

Easiest way to determine how to do something is to record a macro and view the generated code.

Moved from Classic VB.

[guyjasper]

thanks dee-u and Liquid Metal.

Easiest way to determine how to do something is to record a macro and view the generated code.

Moved from Classic VB.

i learned something new from what you said thanks. by the way, from the codes generated when you record a macro, are those codes also applicable in VB? like can i just copy & paste the codes w/o changes?

[RobDog888]

Yes and No. Most of it will be ok but some of it will require complete object references.

'VBA:
Sheet1.Cells(1, 1).Value = "Test"
 
'VB:
Dim oApp As Excel.Application
Set oApp = New Excel.Application
oApp.Workbooks("Book1.xls").Sheets("Sheet1").Cells(1, 1).Value = "Test"

[guyjasper]

ok, i'll keep that in mind. i was playing with excel macro recording and i noticed that it uses "Selection" object. when i looked it up in the object browser (VBA), it belongs to Excel.Window object. how come i can't find it in VB? i already made a reference to Microsoft Excel 11.0 Object Library and there is no Excel.Window object

[RobDog888]

Its parent object is the Application object class.

Application.Selection

[guyjasper]

thanks ....