Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 5th, 2006, 03:47 PM
Authorized User
Join Date: Mar 2005
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default Formatting Excel Cells Within ActiveX script

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.



Old April 10th, 2006, 03:45 PM
Friend of Wrox
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post

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
Old November 30th, 2007, 05:45 PM
Registered User
Join Date: Nov 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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


Old January 28th, 2008, 04:17 PM
Authorized User
Join Date: Jun 2007
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vishwjeet

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")
        objExcelWS = CType(objExcelWB.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        objExcelRG = objExcelWS.Range("A1", "B5")
        objExcelRG.Cells.Font.Italic = True

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


        Dts.TaskResult = Dts.Results.Success
    End Sub

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting the cells from VBA yogeshyl Excel VBA 1 July 20th, 2007 12:26 AM
dts - formatting excel sheet using activex script Najmunnisha SQL Server DTS 2 May 17th, 2007 10:27 PM
ActiveX Control to Recalculate Cells webXtreme Excel VBA 1 April 12th, 2007 03:55 PM
Trouble with formatting text in table cells! Sickopuppie HTML Code Clinic 5 June 21st, 2006 03:17 AM
Formatting e.Item.Cells in Datagrid rstelma ASP.NET 1.0 and 1.1 Professional 18 February 26th, 2006 07:34 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.