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
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr, cdoConfig
dbType = "Sybase"
dbName = "staging"
dbUser = "Secman"
dbPass = "MLEENZI"
outputFile = "C:\Automation\debits2income\sheet1.csv"
email = "ngare@nic-bank.com"
subj = "Report1"
body = "Please see attached!"
smtp = "nicexchg01.nic-bank.com"
smtpPort = 25
sFileName = "C:\Automation\debits2income\debits2incomeExec.sql"
dim fso, conn
set fso = CreateObject("Scripting.FileSystemObject")
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
conn.Open "DSN=core;UID=secman;PWD=MLEENZI"
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 MakeDataFile(outputFile,sqlstr)
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
.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 = "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
WScript.Quit