Replace External Links with Values inside Formulas

Hello,

I have a workbook with multiple sheets. Many of the cells have formulas like "=I3*[Other Book]Sheet1!H5+I4*[Dif Book]!Sheet2!D4+J3". Assuming that [Other Book]Sheet1!H5 = 3 and [Dif Book]!Sheet2!D4 = 2, I would like to make a macro that changes this to "=I3*3+I4*2+J3". It can be manually done by highlighting the external parts of the formula one by one and hitting F9, then saving the formula, but this would take forever.

Maybe:

Please Login or Register  to view this content.

Thanks JOHN H. DAVIS. That gives me a great idea. I'm going to try modifying your code to open all the workbooks listed in the LinkSources and then find and replace their formulas with the appropriate values. It might be tricky because I have to match anything of the form [WB Name]SheetName!LETTER#, but I'll see how it goes.

I'm currently stuck on how to evaluate part of a regex expression. I want to use the pattern matches to reference an external workbook and pull the value. Do you know how to do that. Here is my current code, which is a mess since I'm in the middle of writing it:

Please Login or Register  to view this content.

I finally figured it out. My code is below, for posterity. It goes through and replaces any single-cell external reference in a formula with the appropriate value. It then breaks the connection, so that any cell containing an external range gets converted to a value.

Please Login or Register  to view this content.