.Cells VBA reference issue

I have this VBA below that is designed to loop through each WS and insert the top 4 rows of each data set.

This works fine inside of a with, but that only lets me specify one sheet, instead of going through them all. I took the with out and got "Invalid or unqualified reference". After this I added ActiveSheet. to the 'Cells' Method.

Now I'm getting this error below:

"ODBC driver doesn't support the requested properties"

How do I qualify .Cells now? Or is there an alternative? I'm using excel 2010 and mysql.

Public Function InsertData()

Dim rs As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim WS As Worksheet
Dim strsql As String

Set rs = New ADODB.Recordset
  Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=server.host.com;" & _
    "DATABASE=datatime;" & _
    "USER=boulders;" & _
    "PASSWORD=rocks;" & _
    "Option=3"


For Each WS In ActiveWorkbook.Worksheets

    For rowcursor = 4 To 8
        strsql = "INSERT INTO workflow_metrics (id, code) " & _
            "VALUES (" & (ActiveSheet.Cells(rowcursor, 1)) & "," & _
            "'" & (ActiveSheet.Cells(rowcursor, 2)) & "')"

       rs.Open strsql, oConn, adOpenDynamic, adLockOptimistic

    Next
Next WS


End Function

Answers


I think you want:

Param1.Value = WS.Cells(rowcursor, 1)

etc.


Need Your Help

Why emits CMake a CMP0022 warning although not using LINK_INTERFACE_LIBRARIES?

cmake

In my whole code I never use LINK_INTERFACE_LIBRARIES or something similar. All install, export and link_* command are used without.The only somewhat similar occurrence I was able to grep was in the

Ambiguity issue when deducing function call

c++ compiler-errors function-overloading ambiguous-call

I have the following bit of code which has two versions of the function foo. I'd like if a variable is passed for the foo that takes an AVar type to be called otherwise if a const is passed for the