[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Link With Variable File Name

Hi,

I've a Excel workbook which has in-cell call's to other Workbook cells (i.e. cells in other different workbooks) e.g. ='C:\Documents and Settings\sid\Local Settings\Temporary Files\Arr\[11.01.11 File-London (FIN).xls]Reporting'!$E$29

The workbooks are monthly reports and so the file names referenced change on a monthly basis. At the moment I'm doing a lot of copying a pasting from one work book to another. This is time consiming and error prone

I wish to automate this process and one thought was to build the File name dynamically based on the month which would be set as a variable. Therefore, 11.01.11 would be inputed into a cell and refernced and next month I'd only have to change this one value and bingo all the links would be updated to pull in the correct data.

However, it seems like Excel doesn't like / permit a variable file name in this type of formula

Can anyone provide a work around or a better solution?

Thanks in advance
0
bowemc
Asked:
bowemc
  • 4
  • 3
1 Solution
 
bowemcAuthor Commented:
I've found the INDIRECT.EXT function on the add-in morefunc

http://www.freewarefiles.com/downloads_counter.php?programid=14922

Does anyone know how I could do this via VBA though?
0
 
sstampfCommented:
This code may help you.
Source - http://www.dbforums.com/microsoft-excel/1074340-mass-changing-excel-links.html
Const OldPath = "C:\NewPath\"
Const NewPath = "C:\Old Path\"

Sub UpdateFormulas()
    Dim FormulaCells As Range, Cell As Range
    Dim strFormula As String
    Dim curFormula As String
    
'   Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
    
'   Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If
    
    
'   Process each formula
    For Each Cell In FormulaCells
            curFormula = Cell.Formula
            If InStr(1, curFormula, OldPath) > 0 Then
               strFormula = Replace(curFormula, OldPath, NewPath)
               Cell.Formula = strFormula
            End If
    Next Cell
   
End Sub

Open in new window

0
 
sstampfCommented:
I just tested the code and worked nicely. This is how I altered the code (just the two lines on the top). You need to do the same.

Const OldPath = "C:\Documents and Settings\abc123\Desktop\sst\[File1_ 25509291.xls]Sheet1"
Const NewPath = "C:\Documents and Settings\abc123\Desktop\sst\[File2_code_JAN04.xls]Sheet2"

Open in new window

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
byundtCommented:
Have you taken a look at the HYPERLINK function? It takes text inputs that you can build within your formula.
=HYPERLINK("[C:\Documents and Settings\sid\Local Settings\Temporary Files\Arr\" & A1 & "File-London (FIN).xls]'Reporting'!$E$29", "Click for details")
0
 
bowemcAuthor Commented:
byundt, I don't want to click on a hyper link, I want the value to appear in the cell rather than a hyperlink.
0
 
sstampfCommented:
What about my solution? Did u try it?
0
 
bowemcAuthor Commented:
ok, seems to work actually.

What does this do? Whats the 23 represent?

Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
0
 
sstampfCommented:
This line is used to find only cells having formulas.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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