I have a project in which I need to correct multiple large workbooks with worksheets (in database format) that have cells that sometimes resolve to an error, usually #VALUE! and #NA(). As other workbooks use some of the numeric columns in calculations, I need to remove any errors and replace them with a vbnullstring or empty string. I am trying to avoid opening all the workbooks as this can take a few minutes (there are upto 40 at a time). In the past I have used ADO to update cell values in closed workbooks but I cannot manage to make this work when the cell contains an error. When I interrogate the recordset that is linked to the workbook, errors present as a Null value, just like an empty cell. But if I try to change any Null value, I get an this error - Field Cannot be updated. I am able to update other fields and the connection and recordset open properly. Here is my code:
conn.Open sConn sSQL = "select * from [8.3_BCMI_TCMI$]" rs.Open sSQL, conn, adOpenDynamic, adLockOptimistic For lCtr = 0 To rs.Fields.Count - 1 Step 4 If IsNull(rs(lCtr)) Then rs(lCtr) = vbNullString End If Next rs.Update
The error happens on the rs(lCtr) = vbNullstring line. Is there any way I can achieve my goal? I thought that the problem may be that I am trying to change a null type to a string type but I do not know how to change an ADO data type in a recordset that has already been opened. Is there an alternative way not using ADO? Also, (a separate problem), there are a few places in these workbooks that only have a single cell that I want to update. I know how to retrieve a single cell's value using ExecuteExcel4Macro but was wondering if using ExecuteExcel4Macro that is is also possible to SET a single value.
Sorry for adding in an extra question at the end!
thanks in advance,