asked on

Macro to edit links

Hi.

I have an Excel file with a bunch of cells that are linked to another Excel file. Because the year is included in the link, every year, I have to manually update the links with the new year. Is there a way to edit the links through a macro where I could, for example, enter the new year in an input box and have all of the links updated with the new year. Here's a simplified example of what I'm hoping to do.

Let's day I have an Excel file with the following links in cells B5 through B10:

='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F5 <---B5
='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F6 <---B6
='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F7 <---B7
='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F8 <----B8
='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F9 <----B9
='C:\MyMainFolder\2012\[2012 MyFile.xlsx]MyFolder'!F10 <----B10

I'd like to be able to just run a macro that will present me with an input box (or some other way of entering the year) where I could, say, enter '2013' and have all references to 2012 changed to 2013 so that the end result would be this:

='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F5 <---B5
='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F6 <---B6
='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F7 <---B7
='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F8 <----B8
='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F9 <----B9
='C:\MyMainFolder\2013\[2013 MyFile.xlsx]MyFolder'!F10 <----B10

Thank you

nutsch

No need for a macro: you can update the file directly in Data \ Edit links.

Thomas

Jenkins

ASKER

Unless I'm misunderstanding, that's not what I want. I have many files that need to be updated. And each file is linked to at least 2 other files. I just gave a very simplified example of what I'm trying to accomplish. Ultimately, what I want to do is have all files that need to be updated store in one folder. I will then run a macro that will update them all with the year I choose. Right now though, I'm just trying to find out how to specific characters (ie the year) within the link.

nutsch

Give this a try:

Sub Macro2()
'
' Macro2 Macro
'

Dim sht As Worksheet, sNewYear As String

sNewYear = InputBox("enter new year")

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


For Each sht In ActiveWorkbook.Worksheets

sht.UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:="2012", Replacement:=sNewYear, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
    
Next sht


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Open in new window

Jenkins

ASKER

That appears to be what I'm looking for but when I run it, a windows explorer window is popping up prompting me for a file name. What is that all about?

nutsch

If you do a replace and the new file name cannot be found, you will get a popup asking to choose a valid file.

Is that's what's happening?

T

Jenkins

ASKER

When I run the macro, it prompts me to enter a year. When I enter a year, the program goes through the loop 3 times (even though I have more than 3 tabs in the file with data in them. Why is it stopping after only 3?) Not sure if it is actually going through three tabs (worksheets) or just 3 rows or something else. All I can say is I put a messagebox immediately before the loop to try to see what's going on and it displays 3 times before a Windows Explorer windoe pops up with the the "Look in:" combobox at the top, a list of folder names in the white space and the cursor in the "file name:" combobox apparently prompting me to enter a file name.

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jenkins

ASKER

That one worked. Thank you very much.