Excel information - Validating path 2

[ItIsHardToProgram]

We have an issue when it comes to replacing data in a path.

In fact, when replacing a file name in a path, for example
c:\\MyDocuments\Data.xls

Search/replace: Data.xls with Alpha.xls

.xls will open the file and close it to validate that the path is correct, thus being, replacing a large number, (32 000 cells), takes almost an hour.

Is there a way to shut that automatic validation in excel?

Thank you in advance.

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

 

[ItIsHardToProgram]

FYI I have tried removing the error detection process in options.

Thank you for your time

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

 

[SkipVought]

Hi,

Are you referring to replacing data in a REFERENCE?

Turn off Automatic Calculation .. Tools > Options- Calculation Tab and select MANUAL calculation and UNCHECK calcualte before save.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Thanks for your fast reply again skip,

I believe that I have done that already, but I will do a double check in the morning, until then have a nice evening, I am off work early today, exams


But to answer your question its a followup of what we were talking about in our other conversation.

The line is quite simple,

Value(VlookUp(InternalReference, 'External referance'!range,Column,0 or FALSE)["b]

What I change is the external path, for example I have 30k cells with a path like this:

'S:\Comptabilite\RPR - Budget 2009\FI\[11050_VillaStGeorges_B09.xls]B09 12 mois'!$A:$O,5

I need to change [11050_villaStGeorges_B09.xls] With [12200_Renaissance_b09.xls] This will take about 5 to 10 minutes for 5k cells....

Im looking at a way to do this faster because we have 45 different files and 2 sheets with the references, hence 45 times 2 = 90 sheets with over 12*2600 cells in wich we need to change references.

I am trying to find a way to do this faster.

Thanks for your inputs skip,

Have a very nice and well earned evening.

Julien Roy

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

 

[SkipVought]

There may be a better way. It depends on the structure of the data in your workbook/worksheets. And, of course, it depends what data you are trying to reference with lookups and where it needs to reside et al.

Generally, links to external workbooks is not what I woud recommend, and I reference data from other data sources every day -- DB2, Oracle, MS Access and other workbooks.

In most cases, I QUERY these data sources (and I can do that with other workbook/worksheets because they are all structured as TABLES), using MS Query. faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Very interesting, I did not know there was a Query wizard in Excel, considering I have worked with access in the past, coding MySQL I will see what I can do with that data, Having a outside query is probably the best way to update the data indeed...

I think I will test this out tonight on my laptop and see what I can do tomorow morning.

Ill give you feedback as soon as I stall or if I have finished what I wanted....

Processing it on multiple variable may be different than the test I will do =.-

Thx alot though skip, your gold.

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

 

[ItIsHardToProgram]

Unfortunatly it is not installed here, and i doubt I will have any time tomorow at my job to try this new thing considering I already spent alot of time for a simple value() problem....

not sure how to make my worksheets in tables... but ill figure that out also.

Ill give you feed back any way.

Thank you skip,

Julien

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

 

[GlennUK]

For changing references to external links, such as

'S:\Comptabilite\RPR - Budget 2009\FI\[11050_VillaStGeorges_B09.xls]B09 12 mois'!$A:$O,5

I need to change [11050_villaStGeorges_B09.xls] With [12200_Renaissance_b09.xls]

Open both [11050_villaStGeorges_B09.xls] and[12200_Renaissance_b09.xls], and then activate your original workbook again before doing Edit/Links/Change Source.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[ItIsHardToProgram]

Open both [11050_villaStGeorges_B09.xls] and[12200_Renaissance_b09.xls], and then activate your original workbook again before doing Edit/Links/Change Source.

We thought of that just did not try it, I will try it right away and give you feedbacks.

Skip, we need to get closure on alot of things so I don't think we have time right now to adress the kind of changes you suggested.

Although these changes are probably the best way to do what we want, we dont have time to program a query to pass data from files to other files and only use .xls has last resort, and the issue we have is we have alot of users, so we would have to program something user friendly, most of them don't necesserely understand computer as well as my department.

For now lets close this thread and I maybe reopen later or even higher a consultant, we shall see, for now youve helped a great deal and I thank you.

Pleasure to conversate with you skip,

Julien

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

 

[ItIsHardToProgram]

Works like a charm, everything's perfect, Thank you Glenn

Julien

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

 

[GlennUK]

My pleasure! :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.