How to update cells in a closed workbook that contain an error

Hi,

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,

Chris

The reason that there is a step 4 in the For statement is that the columns that I need to alter occur every 4th column.