excel - Operation not allowed when the object is closed in VBA -
i new site , vba, how managed write vba, got above error. please advise me changes in code. thanks
option explicit const constrsql string = "----" sub copydatafromdatabase() dim dataconn adodb.connection dim staffdata adodb.recordset dim datafiels adodb.field set dataconn = new adodb.connection set staffdata = new adodb.recordset dataconn.connectionstring = constrsql dataconn.open on error goto closeconnection staffdata .activeconnection = dataconn .source = "select ---" .locktype = adlockreadonly .cursortype = adopenkeyset end on error goto closerecordset worksheets.add each datafiels in staffdata.fields activecell.value = datafiels.name activecell.offset(0, 1).select next datafiels range("a1").select range("a2").copyfromrecordset staffdata range("a1").currentregion.entirecolumn.autofit closerecordset: staffdata.close closeconnection: dataconn.close end sub
when remove closerecordset: staffdata.close
i got columns listed in excel, not data
please advise. thanks
your code defines recordset
doesn't open it. adding:
staffdata.open
before:
range("a2").copyfromrecordset staffdata
should solve problem.
Comments
Post a Comment