DTS, Excel Template and formats.

Question

DTS, Excel Template and formats.

rmcknight

SSC Eights!

Points: 918
More actions
June 21, 2006 at 6:34 am

#67598

I use SQL2K & DTS to export to Excel (2003) daily. Using scripting I first open an excel template (Report.xlt) and copy/rename (Report_20060621.xls). The template is already formated, and empty so I do not have to drop/add tables. The template also has multiple named-ranges to export data to.
The problem is keeping the formating. Saving the template with specific columns decimal (.00), percent (%) and font size does NOT persist to the copy. I've had to write macro's so when opening, the formating is put back. I want to get away from the macros.
Is there a way to 'lock' the cell-formats of the template and copy? Does it get lost in the copy, or when the transformation task populates?
thoughts?
rm

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 1

Randy,

I had the same situation and started to use a complicated procedure paste special etc etc. After some thought I found a much more efficient and faster method.

Part of the code:

Dim varmen As Variant

Dim varrec As Variant

Dim I, J, lrows, lcols As Long

CommandText = "SELECT * from data "

rsf.Open CommandText, conn, adOpenKeyset, adLockReadOnly

varrec = rsf.GetRows 'Populate array with the data

rsf.Close

lrows = UBound(varrec, 2) 'Number of rows

lcols = UBound(varrec, 1) 'Number of columns

ReDim varmen(lrows, lcols)

For I = 0 To lrows

For J = 0 To lcols

varmen(I, J) = varrec(J, I) 'Transform

Next J

Next I

excelapp.Worksheets("Master").Select

Range(Cells(1, 1), Cells(lrows, lcols)).Value = varmen 'Paste values only

The nice thing is that there is no slow looping in Excel.

Hope this can be usefull

Gosta Munktell

rmcknight SSC Eights! Points: 918 More actions · Answer 2

Gosta;

Are you saying you use the Excel automation object in an ActiveX task to populate the ranges from code, rather the data transform task itself?

Randy.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

Randy,

My example is originally written in VB6 so there is a

Set appExcel = CreateObject("Excel.Application")

so the answer is yes.

But it should be possible to write it as an

Visual Basic ActiveX Script task.

Ther is an example Owerwriting Data in an Excelsheet.

Gosta

DTS, Excel Template and formats.

Cookies on SQLServerCentral