Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB6 Expected Array error
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\SGWB L.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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB6 Expected Array error
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\SGWB L.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 -- HTH... Jim Thomlinson "Debbie Fleming" wrote: 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\SGWB L.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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ReDim not working as expected. Array expert needed. | Excel Programming | |||
Compile Error: Expected Array | Excel Programming | |||
Expected end of statement error | Excel Programming | |||
compile error - expected an array | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming |