Solved

Sheet Referencing

Posted on 2012-03-12
3
183 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: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 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

Independent Software Vendors: 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

696 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