Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 25-May-11 23:45pm
Comments
Ruard at 27-May-11 3:01am
   
On line 76: how many fields are available? --> showList.fields.count

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,142
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 26 May 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100