VBA Range.Replace not working

Question

The selection is an Excel spreadsheet cell containing the following formula:

=+'[FORECAST MARZO RESTRUCTURING ITALY 20170403 V3.xlsx]DETTAGLIO'!$BA$20

I run the following VBA code:

Selection.Replace what:="[FORECAST MARZO RESTRUCTURING ITALY 20170403 V3.xlsx]", _
Replacement:="[AQ_0617 - solo nuova 272.xlsx]" _
   , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
  :=False, ReplaceFormat:=False

After executing the code, I expected the formula to become:

=+'[AQ_0617 - solo nuova 272.xlsx]DETTAGLIO'!$BA$20

But nothing happens. Any clues?

Answer 1

The replacement method does not work, because 2 excel files do not have the same sheets, i.e. sheet '[AQ_0617 - solo nuova 272.xlsx]DETTAGLIO'does not exist. Therefore, if the replace method was executed, the final formula will contain an invalid link