Excel: edit links on two places: Both filename and directory have new names. Macro??

[Japiejo]

Hi all,

Every month, I need to change 100+ links in Excel by using the edit link file. I cannot do this via Ctlr+H because both the directory and the filename have changed.
Can someone please help me? Maybe write a macro for it or help me do it myself.

FOr example in the file consolidation.xls I have 100+ links to other excel files that look like:
C:\Users\Public\Documents\08.August\USA_ 0814.xls
C:\Users\Public\Documents\08.August\Germany_ 0814.xls
C:\Users\Public\Documents\08.August\France_ 0814.xls

and I need to change it on 2 places, both the month in the directory and the filename itself, so it become:

C:\Users\Public\Documents\09. September\USA_ 0914.xls
C:\Users\Public\Documents\09. September\Germany_ 0914.xls
C:\Users\Public\Documents\09. September\France_ 0914.xls


I hope someone can help me. I am using Excel 2010.

Many thanks,

Jaap

 

[AlphaFrog]

Could you do Ctrl+H twice; once for the directory and once for the file name?

 

[Japiejo]

Could you do Ctrl+H twice; once for the directory and once for the file name?

Hi AlphaFrog, I could do that as well.
That is how I do it now.
First, I copy the august results to september and change the directory. THen I change the filename from 0814 to 0914 in the filename and do another find and replace in Excel.
However, I was wondering if it could go faster.

Thanks

Jaap

 

[AlphaFrog]

Hi AlphaFrog, I could do that as well.
That is how I do it now.
First, I copy the august results to september and change the directory. THen I change the filename from 0814 to 0914 in the filename and do another find and replace in Excel.
However, I was wondering if it could go faster.

Thanks

Jaap

I don't understand why that so difficult? It seems like it would be fast.

Can you record a macro where you do all the steps and post the code here?

 

[Japiejo]

Hi AlphaFrog, I have 20 consolidated Excelfiles which I have to update and some large files takes over 2 hours...for each of the two steps.
Thats the reason I started this post.

I don't know if I am doing it right, but I get this macro for the second step:

Sub Macro1()
'
' Macro1 Macro
'


'
Cells.Replace What:="0814", Replacement:="0914", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[AlphaFrog]

Hi AlphaFrog, I have 20 consolidated Excelfiles which I have to update and some large files takes over 2 hours...for each of the two steps.
Thats the reason I started this post.

That's quite a different problem than discussed in the 1st post. It sounds like an issue of speed rather than how to get it done.

I think you want to disable the links from updating before doing the two replacements. Then re-enable the links after.

Control when external references (links) are updated

Those steps could be recorded in a macro.

 

[Japiejo]

OK, many thanks. I will try it!