Excel - External links

[ItIsHardToProgram]

I have been working on files today and making changes in external links.

I have changed a link partialy, with a search and replace, over 70k cells in a sheet, 140k per workbook, the first 70k I changed the name of the file and the 2nd 70k I kept the same file name and I changed only the sheet name of the reference.

For example:

1st 70K

'S:\Comptabilite\RPR - Budget 2009\FIV\[[highlight]14000_Corpo_B09.xls[/highlight]]B09 12 mois'!$A:$O
replaced with
'S:\Comptabilite\RPR - Budget 2009\FIV\[[Highlight]14130_renaissance_B09.xls[/highlight]]B09 12 mois'!$A:$O

2nd half:

'S:\Comptabilite\RPR - Budget 2009\FIV\[14130_renaissance_B09.xls][highlight]B09 12 mois[/highlight]'!$A:$O

replaced with

'S:\Comptabilite\RPR - Budget 2009\FIV\[14130_renaissance_B09.xls][highlight]A08 12 mois[/highlight]'!$A:$O

The problem I have is simple, the first half of the external links work perfectly but the 2nd half of the external links do not work at all.

I had All the linked sheet i was working on opened to diminish the CPU usage and I proceded that way to save time since when changing the 2nd part I could replace over all 70k but the first part I had to do it 4k per 4k.

Thank you for your patience with me every one.

Julien Roy

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Gavona]

If you create one of those links manually then what does the formula look like? It must be different from the result you got with Search and Replace.

To change the workbook (First part) you would be better using Edit,Links...ChangeSource


Gavin

 

[ItIsHardToProgram]

I will post both the formula, the answer is no and both the tables exist.

Changing source manualy is only for the file reference NOT The table reference, no?

This is my first formula

=-ARRONDI(CNUM(RECHERCHEV(Prev08!$A8,'S:\Comptabilite\RPR - Budget 2009\FI\[11000_Corpo_B09.xls]B09 12 mois'!$A:$O,4,0)),0)

This is the 2nd formula

=-ARRONDI(CNUM(RECHERCHEV(Prev08!$A315,'S:\Comptabilite\RPR - Budget 2009\FI\[11000_Corpo_B09.xls]A08 12 mois'!$A:$O,5,0)),0)

Thanks,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

FYI

ARRONDI = ROUND

CNUM = VALUE

RECHERV = VLOOKUP

Thanks,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Gavona]

Changing source manualy is only for the file reference NOT The table reference, no?

No, create your formula using the function wizard and clicking into the new workbook (11000_Corpo_B09.xls)to identify A08 12 mois'!$A:$O

Compare this formula with the one created by search and replace.

Personally I would use named ranges in the workbook.
Lookup to an entire column ($A:$O) with an argument of False is very innefficient.
If the workbook will be open when formulae are updated then you could use a dynamic named range.

And to speed things up switch calculation to manual while you modify the formulae. Tools, Options, Calculation....

Gavin

 

[ItIsHardToProgram]

All that has been done, we are using lookups until we change our system to MySQL queries probably using MSquery or others.

The problem was not the link, the manual link reference was just fine, it turned out to be a format problem in the file it was looking.

No the workbook wont be opened, and no dynamic named range would not work in this situation,

Thank you any way

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Gavona]

Glad you solved it.

If you need to improve efficiency then you can use named range. If the size/shape of the data ranges changes then (maybe in a BeforeSave event) you can run a macro to update. Along the lines of:

Range("Alldata").CurrentRegion.Name = "Alldata"


Gavin