CODEWITHSUNDEEP
vba
andreas
andreas

Reputation: 207

VBA error in excel

I have the following code:

Sub Stats1()
Workbooks("2006_2007_2008.xls").Sheets("Sheet1").Select
Worksheets("Sheet1").Activate

  Dim my_cell As String
  my_cell = InputBox("Which cell?")

     Dim objConn As ADODB.Connection

     Dim rsData As ADODB.Recordset

     Dim strSQL As String
      Dim mycell As String

  szconnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security   Info=False;Initial Catalog=*****;Data Source=*****"

      'Create the Connection and Recordset objects.
     Set objConn = New ADODB.Connection
      Set rsData = New ADODB.Recordset

    On Error GoTo errHandler

    'Open the Connection and execute the stored procedure

    objConn.Open szconnect

     strSQL = "SELECT *fom mytable"
     objConn.CommandTimeout = 0

   Set rsData = objConn.Execute(strSQL)

   For iCols = 0 To rsData.Fields.Count - 1 

    ActiveSheet.Range(my_cell).Select
   ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + iCols).Value = rsData.Fields  (iCols).Name
  ActiveSheet.Cells.Font.Name = "Arial"
   ActiveSheet.Cells.Font.Size = 8

   Next

    ActiveSheet.Range(ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column),     ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + rsData.Fields.Count)).Font.Bold =    True

  If Not rsData.EOF Then

'Dump the contents of the recordset onto the worksheet

 On  Error GoTo errHandler

ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column).CopyFromRecordset rsData


   If Not rsData.EOF Then

    MsgBox "Data set too large for a worksheet!"

  End If

  rsData.Close



    End If

    Unload frmSQLQueryADO

   Exit Sub

   errHandler:

  MsgBox Err.Description, vbCritical, "Error No: " & Err.Number

 'Unload frmSQLQueryADO

  End Sub

i get the "424 Object required error"...dont know what the issue is...! i think i have added all the correct references

Upvotes: 0

Views: 620

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

rsData is the Record Set returned from the query, not the connection.

Try objConn.Close instead

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

One obvious problem:

strSQL = "SELECT *fom mytable"

should be 

strSQL = "SELECT * from mytable"

EDIT: I tested the code above in a mock-up, and while it ought to be considerably tidied, it does work. Therefore, I suggest the error is in this line:

Unload frmSQLQueryADO

Try commenting the line and seeing if it works.

Upvotes: 1

Related Questions