Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    8

    Unanswered: run time error-91 object variable or with block variable not set

    please help me out i am new for this field i have waiting replys

    Sub updateData()
    Dim activesheet As Collection
    Dim totColumns, totRows, i, j, WDS_id
    Dim prBatchName, prTableQry, inTableQry, prTableQry1, dbQry
    Dim rs As New ADODB.Recordset

    Sheet3.Activate
    prBatchName = "tblProd_AGR_007"
    -------------run time error here in this lines---------
    totColumns = activesheet.Cells(2, 1).CurrentRegion.Columns.Count
    totRows = activesheet.Cells(2, 1).CurrentRegion.Rows.Count

    dbconn.Open strConn
    rs.Open "select * from " & prBatchName, dbconn, adOpenStatic, adLockOptimistic
    For j = 3 To totRows
    WDS_id = activesheet.Cells(j, 1)
    rs.Find "WDS_ID=" & WDS_id

    For i = 2 To totColumns
    rs(activesheet.Cells(2, i).ID) = activesheet.Cells(j, i)
    Next
    rs.Update

    Next
    rs.Close
    dbconn.Close
    MsgBox "Data updated sucessfully"

    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You need to remove

    Dim activesheet As Collection

    ActiveSheet is an object/property of the Excel Application Object, hence it is confusing VBA

    Just using the Key Word ActveSheet in Excel will reference the atcive sheet, sheet3 in the case, (the Application object is the default object and will be searched first for the the object/property if it is not qualified).

    For your info the error message is saying you had not instantiated the activesheet collection object.

    If you had used

    Dim activesheet As New Collection

    you would have recieved the 'Object doesn't supportthis property or method ... ' error message as the the properties of the sheet object are not available in a collection object.

    Hope that makes sense.

    One other comment, CurrentRegion as used here is redundant as the Cell range object can only have one row and column (as far as I know) !?


    MTB

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •