tags:

views:

62

answers:

1

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

+1  A: 

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.

Remou
nope..not it... the SQL executes fine..the above SQL is just an example...
andreas
I have added a further note.
Remou