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:

    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,


  • Hello,

    If I can recall correctly the syntax should be:

    ISNULL ( check_expression , replacement_value ) 

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!