VBA error in excel

Question

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

i am not sure..since the program executes fine,.,.i get the results from the query,,and in the end..i get the error..if i debug..i get nothing.... — andreas
– andreas, Commented Feb 25, 2010 at 5:50

Fionnuala · Accepted Answer · 2010-02-25 11:29:18Z

1

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.

edited Feb 25, 2010 at 11:29

answered Feb 24, 2010 at 19:16

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SierraOscar · Accepted Answer · 2014-11-11 16:05:18Z

0

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

Try objConn.Close instead

answered Nov 11, 2014 at 16:05

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

VBA error in excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related