General DTS question...

[gjsaturday]

I have several DTS packages scheduled to generate an excel spreadsheet and email them to various individuals. They work just great. However, now I'm getting requests to "format" the spreadsheets prior to emailing. I think I can add a step to the job to "format" the spreadsheet prior to emailing, and I even generated the vb code by recording a macro (I know this is cheating, but I'm not as savy with vba as I am with sql). Besides the code to do the actual formating, what else do I need so that the formatting code runs against my specified spreadsheet?

Thanks in advance!

 

[gradley]

The hardest part is done!

What you should probably do is add an ActiveX script to each of the DTS packages that will open this document using the Excel Object Model and perform the necessary formatting.

Please note: Some of the steps in the Macro will not work without some tweaking. I learned this the hard way.

Here is a script that we created to perform a similar function. Hopefully this will answer some of your questions:

Function Main()

Dim oApp 
Dim oBook 
Dim oSheet 
Dim strMonth 
Dim strDay 
Dim g_strFileName

CONST xlDiagonalDown = 5
CONST xlDiagonalUp = 6
CONST xlEdgeLeft = 7
CONST xlEdgeTop = 8
CONST xlEdgeBottom = 9
CONST xlEdgeRight = 10
CONST xlInsideVertical = 11
CONST xlInsideHorizontal = 12
CONST xlSolid = 1
CONST xlThin = 2
CONST xlContinuous = 1
CONST xlAutomatic = &HFFFFEFF7
CONST xlDescending = 2
CONST xlAscending = 1
CONST xlExcel9795 = 43

strMonth = Month(Date)
strDay = Day(Date)

If Len(strMonth) = 1 Then
    strMonth = "0" & strMonth
End If

If Len(strDay) = 1 Then
    strDay = "0" & strDay
End If


g_strFileName = "c:\DirectoryName\CSVFile" & strMonth & strDay & ".xls"

DTSGlobalVariables("strFileName").Value = g_strFileName

SET oApp = CreateObject("Excel.Application")

Set oBook = oApp.Workbooks.Open("c:\DirectoryName\CSVFile.csv")
Set oSheet = oBook.Worksheets(1)



With oSheet
    .Rows("1:1").RowHeight = 25.5
    .Range("A1:F1").Font.Bold = True
    .Columns("A:F").EntireColumn.AutoFit
    With .Range("A1:F1").Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    .Range("A1:F1").Borders(xlDiagonalDown).LineStyle = xlNone
    .Range("A1:F1").Borders(xlDiagonalUp).LineStyle = xlNone
    With .Range("A:F").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A:F").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A:F").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A:F").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A:F").Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A:F").Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Range("A2:F2")
        .Sort .Range("D2"), xlDescending, .Range("A2"), , xlAscending
    End With
End With


oBook.SaveAs g_strFileName, xlExcel9795

oApp.Workbooks.Close

Set oSheet = Nothing
Set oBook = Nothing
Set oApp = Nothing

	Main = DTSTaskExecResult_Success
End Function

Good luck!