0

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.

user101998
  • 241
  • 5
  • 15

1 Answers1

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) 

Rows("18").Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • You are the man! However I have a question. `Application.EnableAlerts = True` gives me an error that says "Object doesn't support this property or method". Do you know if I can simply use `Application.DisplayAlerts = True`? Thanks. – user101998 Jun 30 '18 at 18:40