Running Multiple Queries from an Excel Sheet in VBScript

Question

Hello Scripters from all over, I am currently working on a VBScript to pull Query parameters from an excel sheet and run a Query on an SQL server (Sybase ASE) for each line in the excel sheet.

Currently I have the code that pulls the parameters successfully and runs the first row, but when I begin the next row, the query fails due to "Missing Object", but I have already attempted to keep the ADO Connection open and rerunning a query into the recordset, as well as re-creating the connection for every row in the excel sheet and running the recordset. My code currently is as follows:

Dim MyConnection, MyRecordSet, MyExcel, MyExcelBook, MyExcelSheet
Dim ConnectionString, Parameter1, Parameter2, SQL, I
ConnectionString = "Driver=ODBC Driver;UserID=Admin;Password=Pass;"

Set MyExcel = CreateObject("Excel.Application")
Set MyExcelBook = MyExcel.Workbooks.Open("C:/Test.xls")
Set MyExcelSheet = MyExcelBook.WorkSheets(2)
Set MyConnection = CreateObject("ADODB.Connection")
Set MyRecordSet = CreateObject("ADODB.RecordSet")
MyConnection.Open ConnectionString
I = 2

Do while MyExcelSheet.Cells(I,2).Value <> ""
    Parameter1 = MyExcelSheet.Cells(I,6).Value
    Parameter2 = MyExcelSheet.Cells(I,7).Value
    SQL = "Select * From Table Where Type=" & Parameter1 & " AND Color=" & Parameter2 & ";"
    MyRecordSet.Open SQL, MyConnection
    'Save RecordSet to another Excel sheet'
    MyRecordSet.Close
    I = I + 1
loop

MyConnection.Close
Set MyConnection = Nothing

Is there any way to run another query on the same connection to the database without running into the

Object Required

error?

---

Edit after suggested changes, still have the same error.

Dim MyConnection, MyExcel, MyExcelBook, MyExcelSheet
Dim ConnectionString, Parameter1, Parameter2, SQL, I
ConnectionString = "Driver=ODBC Driver;UserID=Admin;Password=Pass;"

Set MyExcel = CreateObject("Excel.Application")
Set MyExcelBook = MyExcel.Workbooks.Open("C:/Test.xls")
Set MyExcelSheet = MyExcelBook.WorkSheets(2)
Set MyConnection = CreateObject("ADODB.Connection")
MyConnection.Open ConnectionString
I = 2

Do while MyExcelSheet.Cells(I,2).Value <> ""
    Parameter1 = MyExcelSheet.Cells(I,6).Value
    Parameter2 = MyExcelSheet.Cells(I,7).Value
    SQL = "Select * From Table Where Type=" & Parameter1 & " AND Color=" & Parameter2 & ";"
    Dim MyRecordSet
    Set MyRecordSet = CreateObject("ADODB.RecordSet")
    MyRecordSet.Open SQL, MyConnection
    'Save RecordSet to another Excel sheet'
    MyRecordSet.Close
    I = I + 1
loop

MyConnection.Close
Set MyConnection = Nothing

Answer 1

So after much testing of different ideas, I finally got the code to work. The issue happened when MyRecordSet was being closed. The status went back to 0 and when you attempted to re-open MyRecordSet, it would fail with a broken object. Reconnecting an then re-opening MyRecordSet led to the same error. So here is the finished code edit that works.

Dim MyConnection, MyRecordSet, MyExcel, MyExcelBook, MyExcelSheet
Dim ConnectionString, Parameter1, Parameter2, SQL, I
ConnectionString = "Driver=Adaptive Server Enterprise;UserID=Admin;Password=Pass;Pooling=False"

Set MyExcel = CreateObject("Excel.Application")
Set MyExcelBook = MyExcel.Workbooks.Open("C:/Test.xls")
Set MyExcelSheet = MyExcelBook.WorkSheets(2)
I = 2

Do while MyExcelSheet.Cells(I,2).Value <> ""
    Parameter1 = MyExcelSheet.Cells(I,6).Value
    Parameter2 = MyExcelSheet.Cells(I,7).Value
    SQL = "Select * From Table Where Type=" & Parameter1 & " AND Color=" & Parameter2 & ";"
    Set MyConnection = CreateObject("ADODB.Connection")
    MyConnection.Open ConnectionString
    Set MyRecordSet = CreateObject("ADODB.RecordSet")
    MyRecordSet.Open SQL, MyConnection
    'Save RecordSet to another Excel sheet'
    Set MyRecordSet = Nothing
    Set MyConnection = Nothing
    I = I + 1
loop

Now if you ask me if its Database specific, I would not know. But this code works with using the Adaptive Server Enterprise Driver and I did use LankyMart's advice with turning connection pooling off. I understand only setting the 'object = nothing' without closing the connection first goes against standard coding practices, but it works. Thanks to those that helped out.