• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 4
  • 4
1 Solution
 
nutschCommented:
No need for a macro: you can update the file directly in Data \ Edit links.

Thomas
0
 
dbfromnewjerseyAuthor Commented:
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.
0
 
nutschCommented:
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dbfromnewjerseyAuthor Commented:
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?
0
 
nutschCommented:
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
0
 
dbfromnewjerseyAuthor Commented:
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.
0
 
nutschCommented:
Let's try and find out, run the attached macro and check your immediate window (ctrl+G from the VB Editor) when the windows explorer window pops up. The last line should tell you the file that's not found.

Sub Macro2asdf()
'
' Macro2 Macro
'

Dim sht As Worksheet, sNewYear As String, rg As Range

sNewYear = InputBox("enter new year")

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

On Error Resume Next

For Each sht In ActiveWorkbook.Worksheets

    For Each rg In sht.UsedRange.SpecialCells(xlCellTypeFormulas)
        If InStr(rg.FormulaR1C1, "2012") > 0 Then
            Debug.Print rg.FormulaR1C1
            rg.FormulaR1C1 = Replace(rg.FormulaR1C1, "2012", sNewYear)
        End If
    Next rg

Next sht


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

End Sub            

Open in new window


Thomas
0
 
dbfromnewjerseyAuthor Commented:
That one worked. Thank you very much.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now