How to manipulate Excel in ActiveX vbscript.

I am writing an AxtiveX script for a DTS package in SQL Server 2000.

I want to open an Excel spreadsheet, add a tab, add some text and formatting to the sheet, fill the rest of the sheet with the results of a query and then save and close the spreadsheet with the same name.

I have written code that does almost everything except save and close the spreadsheet.  If I make it visible, manually save and close it it is fine but if I try to use oXLApp.close it completes the script but leaves the spreadsheet open.  If I try to lok at it it just opens Excel with no spreadsheet in the body.

Can anyone help?  The code I am using is below:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
          Dim oToProcall
    Dim objXL
    Dim objXLWS
    Dim sSheetName
    Dim strPathToFile
    Dim sDate
    Dim sRow2Text
    Dim iCol
    Dim sCol
    Dim dColWidth
    Dim rRange
    Dim sFieldName
   
   
    sSheetName = "TO_PROCALL"
    strPathToFile = "C:\Documents and Settings\jbarn17\My Documents\Book1.xls"
    sDate = "04/17/2007" 'Format(Now(), "mm/dd/yyyy")
    sRow2Text = "PDP Group Subsidy Insert - " & sDate & " - Data Inserted Into Procall"

    Set objXL =  CreateObject("Excel.Application")
     Set objXL = GetObject(strPathToFile)
    'Set objXLBook = xlApp.Workbooks.Open(strPathToFile)
    Set objXLWS = objXL.Worksheets.Add
    objXLWS.Name = sSheetName
    objXLWS.Range("A1").RowHeight = 4.5
    objXLWS.Range("A2").RowHeight = 18
    objXLWS.Range("A2").Value = sRow2Text
    objXLWS.Range("A2").Font.Name = "Arial"
    objXLWS.Range("A2").Font.Size = 14
    objXLWS.Range("A3").RowHeight = 6
    objXLWS.Range("A4").RowHeight = 27.75

        iCol = 0
    For x = 1 To 50
        iCol = iCol + 1
        dColWidth = 8.43
    Select Case iCol
        Case 1
            sCol = "A"
            sFieldName = "KEY"
        Case 2
            sCol = "B"
            sFieldName = "MEMBER ID"
            dColWidth = 10.29
        Case 3
            sCol = "C"
            sFieldName = "MEDICARE ID"
        Case 4
            sCol = "D"
            sFieldName = "MEMBER LAST NAME"
        Case 5
            sCol = "E"
            sFieldName = "MEMBER FIRST NAME"
        Case 6
            sCol = "F"
            sFieldName = "MEMBER M.I."
        Case 7
            sCol = "G"
            sFieldName = "GENDER"
        Case 8
            sCol = "H"
            sFieldName = "BIRTH DATE"
        Case 9
            sCol = "I"
            sFieldName = "AGE"
        Case 10
            sCol = "J"
            sFieldName = "SITE ID"
        Case 11
            sCol = "K"
            sFieldName = "STATE CODE"
        Case 12
            sCol = "L"
            sFieldName = "COUNTY CODE"
        Case 13
            sCol = "M"
            sFieldName = "TRC"
        Case 14
            sCol = "N"
            sFieldName = "TRANSACTION DATE"
            dColWidth = 10.43
        Case 15
            sCol = "O"
            sFieldName = "SOURCE ID"
        Case 16
            sCol = "P"
            sFieldName = "EFFECTIVE"
        Case 17
                sCol = "Q"
            sFieldName = "ORIGINAL EFFECTIVE"
        Case 18
            sCol = "R"
            sFieldName = "EXPIRATION"
        Case 19
            sCol = "S"
            sFieldName = "AREA CODE"
            dColWidth = 6.57
        Case 20
            sCol = "T"
            sFieldName = "PHONE #"
        Case 21
            sCol = "U"
            sFieldName = "ADDRESS LINE 1"
            dColWidth = 26.86
        Case 22
            sCol = "V"
            sFieldName = "ADDRESS LINE 2"
            dColWidth = 26.86
        Case 23
            sCol = "W"
            sFieldName = "CITY"
        Case 24
            sCol = "X"
            sFieldName = "ST"
        Case 25
            sCol = "Y"
            sFieldName = "ZIP CODE"
        Case 26
            sCol = "Z"
            sFieldName = "SSN"
        Case 27
            sCol = "AA"
            sFieldName = "LANG"
        Case 28
            sCol = "AB"
            sFieldName = "HCFA COUNTY"
        Case 29
            sCol = "AC"
            sFieldName = "PLAN NAME"
        Case 30
            sCol = "AD"
            sFieldName = "DIV"
        Case 31
            sCol = "AE"
            sFieldName = "GROUP NO"
        Case 32
            sCol = "AF"
            sFieldName = "GROUP NAME"
        Case 33
            sCol = "AG"
            sFieldName = "PBP"
        Case 34
            sCol = "AH"
            sFieldName = "PROVIDER"
        Case 35
            sCol = "AI"
            sFieldName = "PROVIDER NAME"
        Case 36
            sCol = "AJ"
            sFieldName = "HCFA CONTRACT"
        Case 37
            sCol = "AK"
            sFieldName = "MEDICAID ID"
        Case 38
            sCol = "AL"
            sFieldName = "LEGAL ENTITY"
        Case 39
            sCol = "AM"
            sFieldName = "NETWORK IPA"
        Case 40
            sCol = "AN"
            sFieldName = "SPECIAL STATUS"
        Case 41
            sCol = "AO"
            sFieldName = "IMPORT"
        Case 42
            sCol = "AP"
            sFieldName = "CATEGORY"
        Case 43
            sCol = "AQ"
            sFieldName = "CALL STATUS"
        Case 44
            sCol = "AR"
            sFieldName = "LAST UPDATE"
        Case 45
            sCol = "AS"
            sFieldName = "CALL DUE"
        Case 46
            sCol = "AT"
            sFieldName = "SWEEP JOB"
        Case 47
            sCol = "AU"
            sFieldName = "MM MEMBERSHIP"
            dColWidth = 9.86
        Case 48
            sCol = "AV"
            sFieldName = "COSMOS"
        Case 49
            sCol = "AW"
            sFieldName = "LOGICAL TERM"
        Case 50
            sCol = "AX"
            sFieldName = "PDP CUTOFF"
    End Select
    rRange = sCol & "4"

    With objXLWS.Range(rRange)
        .Value = sFieldName
        .Font.Name = "Arial"
        .Font.Size = 7
        .WrapText = True
        .ColumnWidth = dColWidth
    End With
       'objXLWS.Range(rRange).Borders(xlEdgeLeft).Weight = xlThin
    Next
   
    objXL.Save
     
    'objXL.Application.Visible = True
    'objXL.Parent.Windows(1).Visible = True

    Set objXlWS = Nothing
    objXL.Application.Quit
    Set objXl = Nothing

    Main = DTSTaskExecResult_Success
End Function
Start Free Trial