I create an excel file within an ActiveX script inside a DTS package.
I would like to format the cells.

Does anyone have any code to change the background color of alternating rows? I know I need to use the range, but I don't know how to figure out what the range should be.

I have data from A1:M1 -->Column headings
A2:to where ever the data ends. --> How do I figure out where the data ends to populate the range object.

Thanks,
Ninel



Thanks,
Ninel

Well currently, Excel has a limit of 2^16 rows. So you could use A2:M65535

Or you could count the number of rows you are going to pump into the excel file, by doing a SQL Execute statement to do a select count(*) from whatever where mycriteria = true
and put the output into a Global Variable. Then use the Global variable in your ActiveX script add 1 and that is your end.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Great to know how to reference the range...but does anyone have the code to do the background formatting?

Thanks!

The following code snippet might help you:

[t]Public Sub Main()
        '
        ' Add your code here
        '
        'MsgBox("Inside Main")
        On Error Resume Next
        Dim objExcelAP As Microsoft.Office.Interop.Excel.Application
        Dim objExcelWB As Microsoft.Office.Interop.Excel.Workbook
        Dim objExcelWS As Microsoft.Office.Interop.Excel.Worksheet
        Dim objExcelRG As Microsoft.Office.Interop.Excel.Range

        'objExcelWB = New Microsoft.Office.Interop.Excel.Workbook
        objExcelAP = New Microsoft.Office.Interop.Excel.Application
        objExcelWB = objExcelAP.Workbooks.Open("C:\Vishwjeet\Address.xl s")
        'objExcelWB.OpenLinks("C:\Vishwjeet\Address.xls")
        objExcelWS = CType(objExcelWB.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        objExcelRG = objExcelWS.Range("A1", "B5")
        objExcelRG.Select()
        objExcelRG.Cells.Font.Italic = True

        objExcelRG.Font.ColorIndex = 4
        objExcelRG.Cells.Interior.ColorIndex = 6
        'MsgBox("Error: " + Err.Description + " Done")
        'Return

        objExcelWB.Save()
        objExcelWB.Close()
        objExcelAP.Quit()

        Dts.TaskResult = Dts.Results.Success
    End Sub

I am still not able to do the colours using hex notation.
- Vishu