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_Failure
     End 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