0

I have an Access database (2003) with a button that opens an excel spreadsheet (also 2003) and dumps data into it. The spreadsheet contains a lot of calculations based on this data. There is also a custom add-in that gets installed when excel is opened. All of this works correctly. The problem is the functions defined in the add-in retain the directory information in the cell value. If I do a find and replace to delete all of these directories, everything works correctly and the functions calculate. Without find and replace, the directory remains and the function returns #VALUE.

In the code that opens excel and installs the add-in, I tried putting a find and replace, but it always returns with something along the lines of, "there is nothing to replace." This is occurring because the spreadsheet isn't loading fast enough for it to update the cells with the formulas. Once the spreadsheet loads, I can then do the find and replace manually.

Does anyone know how to make the worksheet wait until everything loads before executing the find and replace?

I've tried adding a wait using a loop:

Dim clock As Long
    clock = 0
    Do
        clock = clock + 1
    Loop Until clock = 7500000

My code for the find and replace is this:

wbk.Sheets("Calculation").Activate
Cells.Replace What:="'C:\Work\AddIns\Tools.xla'!", Replacement _
    :="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

Does anyone know what I can do to make this work correctly?

Thanks.

JohnT
  • 49
  • 1
  • 8
  • This isn't really a duplicate. I never heard of DoEvents. I would never have searched for DoEvents given that it's a brand new concept for me, which is the only commonality between our two posts. His had to do with pasting images and the solution looks to have been DoEvents. His question wouldn't have even surfaced on my search results given that none of my keywords would be in his thread. I really do appreciate your insight though. – JohnT Dec 16 '14 at 16:40

1 Answers1

1

I removed my timer and added DoEvents as pnuts suggested. This solved the problem! Thank you!

JohnT
  • 49
  • 1
  • 8