Excel - Turn off the window that asks you to change links to a workbook

I generated links to some 100 workbooks but not all of them exist. This is fine because if they do not exist it usually means I don't need it. The links are generated based on string concatenation, and the final step is to paste by value to a cell. A sample of the link may look like this "='P:TEMP[wb1.xlsx]sheet1'!$D$1

"='P:TEMP[wb1.xlsx]sheet1'!$D$1

What I need now, is to remove the first quotation mark to bring the link "alive".

I wrote a macro that does the find and replace in the row

Sub BringAlive()

Rows("18").Select

Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub


The only issue with this is that when a workbook doesn't exist, a window pops out asking me to find the workbook. I need to hit ESC many many times until the code execution finishes. I can't just hold ESC as it will stop the code.

Is there a better solution to hitting ESC?

Thanks.

1 Answer
1

You can try turning off alerts on your code (or just a portion of)

Sub BringAlive ()

Application.DisplayAlerts = False

'Your code here

Application.DisplayAlerts = True

End Sub


Also, you should avoid using the .Select method. (see here for more info)

.Select

Rows("18").Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Application.EnableAlerts = True

Application.DisplayAlerts = True

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.