Results 1 to 7 of 7

Changed Excel data link defaults to first sheet of external reference and not given sheet.

This is a discussion on Changed Excel data link defaults to first sheet of external reference and not given sheet. within the Excel Questions forums, part of the Question Forums category; I have an Excel workbook that is used as a template. It contains references to other Excel workbooks (data links). ...

  1. #1
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Angry Changed Excel data link defaults to first sheet of external reference and not given sheet.

    I have an Excel workbook that is used as a template. It contains references to other Excel workbooks (data links). One example:

    Code:
    =VLOOKUP($A18,'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE,AS$5+1,FALSE)
    Here it is referencing the named range "costcenter_PGE" in the sheet "2012". If I open this template the references don't change (specifically, the sheet name), which is good as they aren't supposed to anyway.

    Now, I have a master workbook that builds other end user workbooks based upon the template. The master opens the template, makes some simple changes (delete columns, for example), then saves the template as a workbook for the end user in a different directory with a different file name. The file PATH for the external references are updated to point to the same directory that the end user workbook is saved to using VBA (Workbook method .ChangeLink)

    That all works well and good (I step through the code and watch the changes happen). However, when I open the newly created end user workbook the external references point to the first worksheet in the external reference by default, for example:

    Code:
    =VLOOKUP($A18,'C:\TESTING\program worksheets\[AD Mktg-Com 2012 R2.xlsb]Instruct'!costcenter_PGE,AS$5+1,FALSE)
    Instead of it looking at the "2012" sheet it defaults to the first sheet in the workbook, "Instruct". The workbooks that are being referenced have multiple sheets because they are filled in by other people and may change. So it's not feasible to have a workbook with a single sheet in it (though that would be very nice, I admit).

    What's happening? Why does Excel change the original, specified sheet of "2012" and replace it with the first sheet of the workbook, "Instruct"? How can I make sure it doesn't default to the first sheet of the referenced workbook so it keeps the sheet name originally given?
    Thanks!

  2. #2
    New Member
    Join Date
    Jul 2012
    Posts
    2

    Default Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    I have a doc where I use this formula in the spreadsheet:
    Code:
    =VLOOKUP($J$6,INDIRECT(SS_Ref),2,FALSE)
    where SS_Ref is defined as:
    ="[Customer_Data.xlsx]User_Data!A:D"
    where I have the Customer_Data auto open when Excel is launched and is a hidden workbook - so once installed the user never sees it

    I tried a variety of Indirect etc with the Customer_Data in a cell on the page and couldn't quote seem to get that to work.

    Your example might look something like this:
    Code:
    =VLOOKUP($A18,INDIRECT(External_ref),AS$5+1,FALSE)
    where you created a named range "External_Ref" and define that range as:
    "
    'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE"

    although if multiple users need to either make sure that source is always in the same place.

    You could write a macro to check that the doc is in the correct place or update the named range given a user supplied file name and location.

    I went with the hidden workbook as it was simpler for multiple users across Mac and Windows versions of Excel - and allows me to control who as the customer data file installed on their system.

  3. #3
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Default Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    Huh...that's a clever way to do it. The option I was thinking of would be a global find/replace using Excel's built-in function (it'd be fast) in the workbook open event. I think I like your way much better It's going to require me to do some rework in a test workbook (in a day or three). Yes, the referenced workbooks would all reside in the same directory -- no need for me to have the referenced workbooks opened, even if hidden.

    I'll report back my results but I think this should work (I hope!).
    Last edited by Air_Cooled_Nut; Jul 5th, 2012 at 06:07 PM.

  4. #4
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Default Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    Quote Originally Posted by mcwsky09 View Post
    I have a doc where I use this formula in the spreadsheet:
    Code:
    =VLOOKUP($J$6,INDIRECT(SS_Ref),2,FALSE)
    where SS_Ref is defined as:
    [FONT=Lucida Grande]="[Customer_Data.xlsx]User_Data!A:D"
    where I have the Customer_Data auto open when Excel is launched and is a hidden workbook - so once installed the user never sees it
    ...
    For INDIRECT to work, the referenced external workbook must also be opened according to the Help file:
    If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
    I confirmed this to be true. But what you suggested gave me an idea...

    As a test, I entered this into my cell's formula
    Code:
    =VLOOKUP($A19, TESTING, BJ$5 + 1, FALSE)
    and created a named variable, TESTING, to be
    Code:
    ='C:\ExcelWork\Round 2 - final\program worksheets\[P&E 2012 R2.xlsb]2012'!costcenter_PGE
    (exactly as displayed, no surrounding quotes!)
    That worked quite nicely!

    Thanks mcwsky09 for the help!

  5. #5
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Default Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    Due to time constraints and additional coding involved during the user workbook creation process I've defaulted to using the below code to 'fix' the issue:
    Code:
    Function FixFirstSheetNameInFormulas(sWrongName As String, sCorrectName As String)
    'For some reason the external links will default to pointing to the first sheet of the external workbook.
    'This very specific function will find a linked sheet name and replace it with the proper name using Excel's built-in Find/Replace function.
    
    On Error Resume Next  'Not worried about errors for this function
    ThisWorkbook.ActiveSheet.Cells.Replace What:="]" & sWrongName & "'", Replacement:="]" & sCorrectName & "'", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    On Error GoTo 0  'Resume normal error checking
    End Function
    The function is called by the offending sheet's Worksheet_Activate() event. Luckily, once the text has been corrected, subsequent calls are not noticeable as there is no sWrongName value on the sheet. The replacement seems to 'stick' because if I save the workbook after clicking the offending tabs, quit, then re-launch the workbooks the required values remain.

    Keeping subscribed to this thread in hopes someone can explain what's going on and propose a proper fix since my solution is more of a [effective] hack.

  6. #6
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Thumbs up Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    So...changes had to be made so my above Find-n-Replace was scrapped. Long story made really short, the data links were made into named ranges (named references?) and that name was used in place of the actual filepath/name -- I followed mcwsky09's suggestion. This allowed my code to build the name and its definition for each end user workbook.

    For example:
    This formula
    Code:
    =VLOOKUP($A18,'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE,AS$5+1,FALSE)
    now looks like this
    Code:
    =VLOOKUP($A18,mktg_com1costcenter_PGE,AS$5+1,FALSE)
    and the named range is built like so:
    Code:
    Dim lYear1 as Long, lYear2 as Long, wkbkNew as Workbook, sWkbkName as String, sDefinedName as String, sRangeName as String, sSaveToPath as String
    With wkbkNew
         ...
         .Names.Add Name:=sDefinedName & "1" & sRangeName, RefersToR1C1:="='" & sSaveToPath & "[" & sWkbkName & " " & lYear1 & "-1.xlsb]" & lYear1 & "'!" & sRangeName  'Year 1 named reference
         .Names.Add Name:=sDefinedName & "2" & sRangeName, RefersToR1C1:="='" & sSaveToPath & "[" & sWkbkName & " " & lYear1 & "-1.xlsb]" & lYear2 & "'!" & sRangeName  'Year 2 named reference
         ...
    End With
    Notice how the name is added, by using RefersToR1C1, and how it's built. When looking at the Name in the Name Manager (from the Formulas group in the Ribbon) the Referes to: looks like this:
    Code:
    ='C:\ExcelWork\Round TEST 2\[AD Mktg-Com 2012-1.xlsb]2012'!costcenter_PGE
    There are NO surrounding quotes, this is important. Done!

  7. #7
    New Member
    Join Date
    Oct 2004
    Location
    Portland, Oregon
    Posts
    36

    Default Re: Changed Excel data link defaults to first sheet of external reference and not given sheet.

    (Users are only allowed 10 minutes after a posting to edit it -- fail! Thus this post.)
    This allowed me to build the references as needed, with greater flexibility, and also insured naming consistency. The actual sheet that is being referenced no longer changes -- it can't -- since it's hard-coded in the named reference. Because there are multiple years and "rounds" throughout the year this method also insures that the workbook will reference the correct external workbooks since, again, the path in the named reference cannot easily change at Excel's whimsy.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com