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 3 (of 3 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