Search code examples
vbaexcelfind-replace

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

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.

Solution

  • 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) 

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