Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-05 : 08:10:55
|
| James Quast writes "I have a task to reformat the columns in an Excel spreadsheet output file from within a DTS package. The package is an interface file that migrates data to another server and the last task is to produce a balance report in excel. My user is requesting that the columns be formatted based on the current manual way of running a macro within the spreadsheet. Unfortunately I cannot find any examples on how to do this. Does any one have any examples I could follow? Keep in mind that the DTS package recreates the excel spreadsheet so any macros would be lost. Below is what I have written in a macro.Option ExplicitSub CloseReviewReformat()'' This will reformat the layout of the excel spread sheet to match the' current layout from the old LRS Citrix environment. This will be ran' from a DTS package.CloseReviewReformat Macro' Macro recorded 12/22/2004 by Jim Quast' 'Format LTV to a percent Columns("C:C").Select Selection.NumberFormat = "0.00%" 'Format DCR to a number Columns("C:C").Select Selection.NumberFormat = "0.00" 'Format Analysis Date Columns("E:E").Select Selection.NumberFormat = "dd-mmm-yy" 'Format 'Calced Next Review Date Columns("H:H").Select Selection.NumberFormat = "dd-mmm-yy" 'Format Import Date Columns("M:M").Select Selection.NumberFormat = "dd-mmm-yy" 'Set column heading color Range("A1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Columns("A:N").Select Columns("A:N").EntireColumn.AutoFit Range("F3").SelectEnd Sub" |
|
|
|
|
|
Using ActiveX Scripts in SQL DTS to Reformat output. File