How to add a header/footer in excel file
-
hi,
is there a way for me to specify a header/footer for the excel file that i'm going to create? i'm planning to dump the records into an excel file but my user wants some formatting included with it....can anyone point me in the right direction on how i should go about it...i only know how to dump the data into an excel file and that's it =)
thanks and regards.
ann
-
You will need to create the excel object in an activex script. Then refer to the excel model to find out how to output text to headers and footers.
To get an idea of the code required, record a macro in excel where you put text into a header and then look at the code it builds. To get you started, here is a little script to populte the first line in a spreadsheet:
-------------------------------------------------------------
' Initialise dimensions
Dim appExcel
Dim newBook
Dim oSheet
'Set excel objects
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
' Populate heading columns for mapping purposes and display in excel
oSheet.Range("A1").Value = "Col1"
oSheet.Range("B1").Value = "Col2"
oSheet.Range("C1").Value = "Col3"
oSheet.Range("D1").Value = "Col4"
With newBook
.SaveAs "c:\test.xls"
.save
End With
appExcel.quit
Set appExcel = nothing
Set newBook = nothing
Set oSheet = nothing
-------------------------------------------------------------
This will create new spreadsheet call test.xls on your c drive and populate the cells A1, B1 etc...
If you have a problem working out how to get text into headers or fotters, let me know
------------------------------
The Users are always right - when I'm not wrong! -
Do you mean page header/footer, or just headers for the data columns?
You could,
a) just create a tab delimited text file. Excel opens these fine. You get the column headers but there is no other formatting.
b) create a "template" Excel file that contains the headers and formatting. Prior to exporting your data create a copy of the file using and ActiveXScript task.
Method b would also allow you to have pre-defined page header and footers.
--------------------
Colt 45 - the original point and click interface
-
i am referring to page header/footer. hmmmmm, i never even thought of using an excel template where i will put the customized page header/footer then i will use that template in dumping my records.
and it sounds much easier than doing the activex scripts.
ann
-
Don't save the file as an actual template file (xlt). DTS won't know what to do with an xlt file. Keep it as an XLS file then use an short ActiveXScript task (it's easy once you know how) to create a copy each time you run the package.
Here's a quick little script to create a new copy,
Function Main()
Dim oFS ' File system object Dim sFilename Dim sTemplate
' create a new file from the template Set oFS = CreateObject("Scripting.FileSystemObject")sTemplate = "<path and filename for template file>" sFilename = "<path and file name as specified in destination connection>"
On Error Resume Next oFS.Copyfile sTemplate, sFilename iErrNum = Err.Number sErrDesc = Err.Description On Error Goto 0
If iErrNum = 0 Then ' signal task success Main = DTSTaskExecResult_Success
Else sMsg = "Could not copy " & sTemplate & " to create new file called " & sFilename & vbCrLf sMsg = sMsg & "Error:" & CStr(iErrNum) & " - " & sErrDesc DTSGlobalVariables("ErrMsg").Value = sMsg ' signal task Failure Main = DTSTaskExecResult_FailureEnd If
Set oFS = Nothing
End Function
Put this in an ActiveXScript task before the datapump task. You'll also need a Global Variable called ErrMsg to hold the error message if the copy fails. Using this method you'll need to make sure the file doesn't already exist before copying the new file.
You could also make the script a bit more dynamic by storing the template location and the new filename in global variables. Then you use a Dynamic Properties task to assign the newly created filename to the destination datasource property. With the locations stored in Global Variables you can pass in different values from the DTSRUN command line.
--------------------
Colt 45 - the original point and click interface -
Actually I was just reading an article on a similar approach to creating Excel reports from SQL Server online the other day (although I forget exactly where), but it described the whole process using a DTS job. Basically what he did was just DROP the table from the Excel datasource (he said it didn't work to delete the records) and then create the desired table within the existing spreadsheet (template).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply