Problem extracting data from a sybase database using VBScript

See more:

VBScript

Am getting the error "Operation is not allowed when the object is closed line 85" when running a vbscript that extracts data from a sybase database. The script is calling an sql query saved separately in a file.

The script looks like below: please assist

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Automation\debits2income\debits2income.sql", ForReading)

Yesterday = DateAdd("d", -1, Now)
ReportDate = FormatDateTime(Yesterday, 1)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "XXXXXX", ReportDate)

Set objFile = objFSO.OpenTextFile("C:\Automation\debits2income\debits2incomeExec.sql", ForWriting)
objFile.WriteLine strNewText
objFile.Close


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: Tim@nic-bank.com                        '
' Version Tracker:                                              '
'                                                               '
'                                                               '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr, cdoConfig

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "Sybase"                 ' Valid values: "oracle", "sqlserver", "mysql"
'dbHost = "core"
              ' Hostname of the database server
dbName = "staging"                 ' Name of the database/SID
dbUser = "Secman"               ' Name of the user
dbPass = "MLEENZI"               ' Password of the above-named user
outputFile = "C:\Automation\debits2income\sheet1.csv"      ' Path and file name of the output CSV file
email = "ngare@nic-bank.com"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
  subj = "Report1"          ' The subject of your email; required only if you send the CSV over email
  body = "Please see attached!"    ' The body of your email; required only if you send the CSV over email
  smtp = "nicexchg01.nic-bank.com"        ' Name of your SMTP server; required only if you send the CSV over email
  smtpPort = 25                   ' SMTP port used by your server, usually 25; required only if you send the CSV over email
'sqlStr = "SELECT * from ad_atm_card_status"  ' SQL statement you wish to execute
sFileName = "C:\Automation\debits2income\debits2incomeExec.sql"

'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
conn.Open "DSN=core;UID=secman;PWD=MLEENZI"
'conn.Open "DSN=coredrp;UID=sa;PWD=coredrpsa"

Set Fil = fso.OpenTextFile(sFileName)
Do Until Fil.AtEndOfStream
sqlStr = sqlstr & " " & Fil.ReadLine
Loop

sub MakeDataFile(fPath, sqlstr)
    dim a, showList, intcount
    set a = fso.createtextfile(fPath)

    set showList = conn.execute(sqlstr)
    for intcount = 0 to showList.fields.count -1
        if intcount <> showList.fields.count-1 then
            a.write """" & showList.fields(intcount).name & ""","
        else
            a.write """" & showList.fields(intcount).name & """"
        end if
    next
    a.writeline ""

    do while not showList.eof
        for intcount = 0 to showList.fields.count - 1
            if intcount <> showList.fields.count - 1 then
                a.write """" & showList.fields(intcount).value & ""","
            else
                a.write """" & showList.fields(intcount).value & """"
            end if
        next
        a.writeline ""
        showList.movenext
    loop
    showList.close
    set showList = nothing

    set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = Nothing


finalFile = "C:\Automation\debits2income\sheet1.xls"
macroFile = "C:\Program Files\Microsoft Office\OFFICE11\XLSTART\personal.xls"
sendFile = "C:\Automation\debits2income\Debits2Income.xls"


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts=False

Set objWorkbook2 = objExcel.Workbooks.Open(macroFile)
Set objWorkbook3 = objExcel.Workbooks.open(outputFile)

objWorkbook3.SaveAs finalFile, 2
objWorkbook3.Close


Set objWorkbook = objExcel.Workbooks.open(finalFile)

objExcel.Run "'personal.xls'!debits2income"

WScript.Sleep(10000)

objExcel.Quit


if email <> "" then

    Set cdoConfig = CreateObject("CDO.Configuration")

    With cdoConfig.Fields
        .Item(sch & "sendusing") = 2 ' cdoSendUsingPort
        .Item(sch & "smtpserver") = "nicexchg01.nic-bank.com"
        .update
    End With

    Set cdoMessage = CreateObject("CDO.Message")

 cdoMessage.AddAttachment sendFile

    With cdoMessage
        Set .Configuration = cdoConfig
        .From = "sekit@nic-bank.com"
        '.To = "muragurim@nic-bank.com,susan.mucheru@nic-bank.com,victor.akidiva@nic-bank.com"
        .To = "sekit@nic-bank.com"
    .Subject = "Debits To income Report"
        .TextBody = "Please find attached the debits to income report. Please ignore it, am just testing its automation."
        .Send

    End With


    Set cdoMessage = Nothing
    Set cdoConfig = Nothing

end if

'You're all done!!  Enjoy the file created.
'MsgBox("Data Writer Done!")
WScript.Quit