Problem with a path\filename

[gang-tsou]

I have a problem with the consolidate command.

Selection.Consolidate Sources:= _
"'C:\Travaux Charles\Gis 114\[Gis114-10-21-TP7-macros pour les CAR.xls]CAR Ameublements Fred inc.'!R1C1:R23C3" _
, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

The problem is that function refer to a file in a directory, if i change the file to a new directory i get an error message : cannot open file, 'cause excel cannot find the file where it is supposed to be. What can I do ?

 

[Bruno_x]

Hi gang-tsou,
Welcome to the board !

If the file that you need is in the same map as the file where you use the formula, you could use some code like :

MyPath = thisworkbook.Path & "\"
Selection.Consolidate Sources:= _
MyPath & "[Gis114-10-21-TP7-macros pour les CAR.xls]CAR Ameublements Fred inc.'!R1C1:R23C3" _
, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False