Solved

Sheet Referencing

Posted on 2012-03-12
3
149 Views
Last Modified: 2012-03-27
Good afternoon,
Ive got one of our users who want to update values from another sheet, the issue is the other sheet in in another folder.

I know to reference another Excel document I can simply use spicify the complete folder and then the sheet, but if I have my workbook in one folder up and then into another, how can I reference that?

I would have thought I could use "'..\My Second folder\[myReferencedWorkbook.xls]Sheet1'!$C$9" (go up from my current folder and then into 'My Second folder', but it doesnt seem to work.

So for example:-
Test
+---My First folder
¦        My First Workbook.xls
+---My Second folder
           myReferencedWorkbook.xls


Is is possible to do referencing like this, or do I need to use the full path when moving up and down folder paths?

Thank you
0
Comment
Question by:tonelm54
3 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37710614
Sorry, you need to use the full path.
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37710688
Unfortunately, MS Excel doesn't allow relative path referencing. See this old discussion for more detailed explanations.

If you're familiar with Excel Macros and VBA, you could use a VBA routine to get the path of the current worksheet, parse up one level, and append the relative path. Then you could use the Workbook object's .LinkSources and .ChangeLink methods, if necessary, to do the updating easily. It would also be possible, though more clumsily (and usually much slower) to parse the filepath fully and then compose a new formula to inject into worksheet cells, if updating links directly after the workbook has already been opened (and security settings and user intervention possibly having already messed with the links) is not a reliable option.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37711162
Here's a sub I use when I need to do this and its tedious doing it manually (re: i have more than one link, or I keep getting the pop-up to select the source).  I made a few modifications to get you started with your references:
Sub changeAllLinks()
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim oldName As String
Dim newName As String
Dim i As Long
Dim myWkb As Workbook
Dim startTime As Long
Dim endTime As Long

    startTime = Timer
    Application.DisplayAlerts = False
    Set myWkb = ThisWorkbook
    
    aLinks = myWkb.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            If aLinks(i) Like "*myReferencedWorkbook.xls*" Then 'modify this to find the old references
                oldLink = aLinks(i)
                oldName = getFileName(oldLink)
                newLink = onePathUp(ActiveWorkbook.Path) & "\mySecondFolder\" & oldName 'modify this to change the folder location
                If oldLink <> newLink Then
                    myWkb.changeLink Name:=oldLink, newName:=newLink
                End If
            End If
        Next i
    End If
        
    Application.DisplayAlerts = True
    
    endTime = Timer
    MsgBox "Process Complete in: " & Format((endTime - startTime) / 60, "#.00") & " Minutes"
End Sub
Function onePathUp(strPath As String) As String
    onePathUp = Left(strPath, Len(strPath) - InStr(StrReverse(strPath), "\"))
End Function
Public Function getFileName(strPath As String) As String
Dim i As Integer
    i = InStr(StrReverse(strPath), "\")
    getFileName = StrReverse(Left(StrReverse(strPath), i - 1))
End Function

Open in new window


Cheers,

Dave
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now