VB6 Expected Array error

[Debbie Fleming]

Hello all!
I am a newbie VB6 programmer.
This part of my program is going to the SQL server to get data and then
taking that data and entering it into an Excel spreadsheet. (Excel 2003)
I am getting an "expected array" compile error on this line:
objWorkSheet.Cells(Row, 1) = rsData("MachineNum")

Thanks in advance for any insight into my problem(s).

Here is my code:

Private Sub cmdrunreport_Click()
Dim SQLdb1 As New Connection
Set SQLdb1 = New Connection
dDate1 = txtdDate1.Text
dDate2 = txtdDate2.Text
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objWorkSheet As Excel.Worksheet
Dim rsData As String
Dim Row As Long
Row = 0


'Open Excel file
Set objExcel = New Excel.Application
Set objWorkBook =
objExcel.Workbooks.Open("S:\Assembly\Bondline\SGWBL.xls")
Set objWorkSheet = objWorkBook.Worksheets(1)

'Retrieve the data from sqlserver
SQLdb1.Open "Data Source=probe-srv1;Initial Catalog=Assembly;User
ID=bpotoeski;password=brian;Persist Security Info=False"
rsData = "SELECT MachineNum, dDate, BondLine FROM SGWBL; " & _
"WHERE dDate Between 'dDate1' AND 'dDate2'"
MsgBox " I have the data"

'loop through the data
Do Until EOF(rsData)


'increment row number
Row = Row + 1

'write data to worksheet
objWorkSheet.Cells(Row, 1) = rsData("MachineNum")<--ERROR
objWorkSheet.Cells(Row, 2) = rsData("dDate")
objWorkSheet.Cells(Row, 3) = rsData("BondLine")

rsData.MoveNext


'Save and close file
objWorkBook.Save
objWorkBook.Close

'cleanup
Set objWorkSheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

'close the database
SQLdb1.Close

End Sub

 

[Jim Thomlinson]

There are lots of issues in your code. The specific error you are getting is
because you have declared reData as type string so
rsData("MachineNum")
makes no sense.

You need to create a connection to the server and then populate a recordset
with the data. Then you can copy the data to a worksheet...

More like this but it will probably need more work...

Private Sub cmdrunreport_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set SQLdb1 = New Connection
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objWorkSheet As Excel.Worksheet
Dim Row As Long

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Row = 0
dDate1 = txtdDate1.Text
dDate2 = txtdDate2.Text

'Open Excel file
Set objExcel = New Excel.Application
Set objWorkBook = objExcel.Workbooks.Open("S:\Assembly\Bondline\SGWBL.xls")
Set objWorkSheet = objWorkBook.Worksheets(1)

'Retrieve the data from sqlserver
cn.Open "Data Source=probe-srv1;Initial Catalog=Assembly;UserID=bpotoeski;"
& _
"password=brian;Persist Security Info=False"
rs.Open "SELECT MachineNum, dDate, BondLine FROM SGWBL " & _
"WHERE dDate Between '" & dDate1 & "' AND '" & dDate2 & "'", cn, , , adCmdText

objWorkSheet.Range("A1").CopyFromRecordset rsData


'Save and close file
objWorkBook.Save
objWorkBook.Close

'cleanup
Set objWorkSheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

'close the database
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub