File location refence disappearing

[rrenis]

Hi - I've got the following in a Sub that opens Summary.xls, copies in a row that contains a reference to template_cost_breakdown and replaces this refence with myFile so that a link to myfile is created. Unfortunately when the Summary.xls sheet has been opened with template_cost_breakdown.xls the E:\Summary\Costs\ part of the refence is lost.

This doesn't happen when running the VBA code as I rename template_cost_breakdown.xls prior to opening the Summary.xls - it's just if they happen to be opened at the same time for any reason.

Does anyone know if you can stop excel from altering the link to template_cost_breakdown .xls in the Summary spreadsheet if they are both open? Failing that could anyone help with suggesting a way of using something like If E:\Summary\Costs\ exists then run the following code else run the code excluding the reference to E:\Summary\Costs\

Selection.Replace What:="E:\Summary\Costs\[template_cost_breakdown.xls]", Replacement :=myFile, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Many Thanks,
rrenis

 

[rrenis]

Thanks hatman - been tweaking the sheet a bit so the standard text is returned from the sheet...

: a : "='E:\Summary\Costs\Select Name\[Enter Name of Project, and Location. - Enter Reference Number.xls]Costs 1'!$C$16" : Variant/String

Hope this will shed some light on the problem.

Cheers,
rrenis

 

[rrenis]

Hi hatman - made a mistake when typing up the return on the previous post - (should have just used paste!) it should have read Name of Project's - and I think this is where it's falling down this time as I tried it again actually entering projects not project's and it worked!!

Is there a way that this could work even if the name of the various projects contains an apostrophe and s ??

cheers,
rrenis

 

[hatman]

HA HA!

No, you can't use an apostrophe anywhere in the file/pathname. It has nothing to do with my code, and everything to do with the fact that Excel uses the apostrophe (or single quote) as a delimiter to indcate where the file/pathname starts and ends when there are spaces in the text string.

I suppose there may be a way around it (one of the gurus around here proabbly has a trick that I don't know :wink: )... but quite frankly, it is easiest to simply eliminate the use of the apostrophe... consider it an invalid character and you will be safe.

In fact, if it is a danger, I would recommend adding some code, just after myfile gets generated, which looks something like this:

 if instr(1,myfile,"'") > 0 then
    msgbox "Please rename your file to eliminate the use of the apostrphe(s) and restart" , , "Exitting"
    exit sub
end if

 

[rrenis]

Hi Hatman - thanks again for getting back to me - your help has been fantastic

I'll add the code you suggested for the apostrphe and on top of your code for renaming of the link it looks like I've got some rock solid code so thank you very, very much for your help!!!

Cheers,
rrenis