Solved

Sheet Referencing

Posted on 2012-03-12
3
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

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 42

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 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