?
Solved

Sheet Referencing

Posted on 2012-03-12
3
Medium Priority
?
194 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 35

Expert Comment

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

Expert Comment

by:Frank White
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 42

Accepted Solution

by:
dlmille earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

862 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