Excel 2010 "replace all" is acting like "replace" when formula contains link to workbook not created yet.

Question

I have workbooks that are created everyday to record production filename:"production 10-31-11.xlsx". I use a summary workbook which has a worksheet for evary day of the month and are numbered to match the day it references, each worksheet  uses links to the "daily" workbook to extract information. I have used this for years with no problems. To update the summary for the next month I would always use a find and replace all to replace " 10-" with " 11-". With excel 2007, I would right click and "select all sheets, then perform the replace all and it would prompt once per worksheet saying it couldnt find the worksheet in the formulas link, hit cancel, then it would update all formulas on the sheet and move on to the next. In 2010 whenever it encoutners a link to a workbook that doesnt exist yet it prompts on every single cell and opposed to once per sheet. meaning I can no longer use this mehtod becuase I owuld have to hit enter 430,000 times to update my summary sheet. Is there an option I can select to make this work like excel 2007?

Answer 1

Does anyone know if there has been an update on this? I still have this issue.

Answer 2

I still need help with this please!!!!!

Excel 2010 "replace all" function is operating differently from Excel 2007.

I have links which point to external workbooks which are created everyday with the date i nthe filename. i.e. "workbook 01-01-2012.xlsx"

In the master work book, which I am trying to update, I have 31 worksheets, one for every day possible in a month. Each  worksheet has links to that days file.

When I try to update the master at the beginning of the month I use find and replace, for instance I would find " 01-" and replace with " 02-".

In excel 2007 when a file that didnt exist yet was encoutnered I receive a prompt to and an explorer window. I can either browse to a file that exists or hit cancel and continue with the replace function. When more than one worksheet is selected I will only receive this prompt once per worksheet (as each worksheet is looking for a different file) and the replace all routine runs and I am happy having to hit enter 31 total times to complete my routine.

In excel 2010 I still only get the prompt once per worksheet but it is only replacing one cell at a time so I have to hit enter or press replace on every cell so I would have to hit enter over 1 million times to replace all the links. WHY??????????? and more importantly what can I do about it?

Answer 3

If Excel 2007 worked differently, it was the unusual version. Excel 2003 and prior would display the dialog for every cell in every worksheet with a formula link to a nonexistent file. Looks like Excel 2010 is just reverting to previous behavior.

Better to do this with a macro, which could be stored in a different workbook since macros can't be stored in .XLSX workbooks. Something like

Sub foo()

  If Not TypeOf Selection Is Range Then Exit Sub

  On Error GoTo CleanUp

  Application.DisplayAlerts = False  'this prevents the file selection dialogs from appearing

  Selection.Replace <filename-placeholder>, <specific-filename>

CleanUp:

  Application.DisplayAlerts = True

End Sub

Answer 4

There summary workbook "November Production Summary.xlsx" has worksheets for every day of the month (i.e. 1-30). Each worksheet has a range of about 500 cells each with a link to specific cells in the daily production workbooks. The daily production workbooks "Production 11-01-11.xlsx" etc. are created on the date specified in the file name. So, for example, the workbooks referenced in the links on tab 30 of the summary wont be created until November 30th.

I would perform this routine on say 10-31-11 to create the summary workbook for November, so none of the daily production books have been created at that point.

In excel 2007 when I would create the summary file for the next month, I would Select all Sheets in the work book (i.e.1-30), go to find and replace, find " 10-" and replace with " 11-". Once replace all was pushed it would pop-up a file browsing window as it couldnt find any of the next months files because they arent created until the day they are used. If I hit the cancel button in the file browsing window, replace all would just replace every link that had "10-" in it with " 11-" which is what I need to have happen, the file browser would only pop-up once per worksheet, since each workshete points to only one file, it would only bring up the file browser once per sheet, no matter how many cells actually reference that sheet. So basically to update the summary file to find the next months daily workbooks I would hav eto hit cancel once per worksheet or 30 times.

Excel 2010 is different as it is bringing up the file browser for every single cell that links to a daily production workbook which hasnt been created. So 500 times per worksheet times 30 worksheets for the month. This I cant do...

Answer 5

What do you mean by "when formula contains link to workbook not created yet"?