Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel links square brackets (Excel 2003)

    I'm posting in VBA because I think this is not really an end-user Excel question. It's affecting me because I'm writing VBA code to analyse the quirks of Microsoft Excel. Moderator(s) please feel free to move it to Excel if you'd prefer it there.

    What makes Excel (2003 in this case) decide to use square brackets, or not?

    I have some VBA code which happily detects a file name enclosed in square brackets, such as <font color=red>='C:Blotter20061105[KO.RAW]Summary'!$A$2</font color=red> and replaces the file name. A crude but effective way of building a summary sheet from 200 contributing data sheets.

    I find that a straight reference such as the one above generates square brackets.
    However, use of the VLookUp function generates a reference to the file without square brackets as in <font color=red>=VLOOKUP(D17,'C:Blotter20061105KO.RAW'!S ummary_Data_Range,2,FALSE)</font color=red>

    I find nothing in the help files to indicate a rule as to why square brackets get issued to certain formulae, not to others.

    In my case I opened the summary and the detail workbooks, created both formulae by clicking in the appropriate cell(s) in the data workbook, then exited Excel, saving, reloaded Excel, opened the summary book to reveal the formulae shown above.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel links square brackets (Excel 2003)

    The first formula refers to a cell address, the second to a named range.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel links square brackets (Excel 2003)

    <P ID="edit" class=small>(Edited by chrisgreaves on 06-Nov-06 21:21. )</P>> The first formula refers to a cell address, the second to a named range.
    You seem to be right; "seem to be" because a quick experiment verifies yourtatement. But where is the definitive word on this from Excel? How did you know what to say?
    More research dug up this from Excel 2003 Help says:
    Find links in a workbook
    There is no automatic way to find links used in a workbook. However, because links use brackets [ ] to enclose the source workbook (source file: The file that contains information that was used to create a linked or embedded object. When you update the information in the source file, you can also update the linked object in the destination file.) name when it is closed, you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

    I see that once the source/detail workbook is closed, the path is included in the link definition, but the address/namedrange qualification holds good. Significantly the help file suggests that links use brackets [ ] to enclose the source workbook always, which is clearly not always the case.
    In my example I based my code on the client's novice knowledge of Excel. My mistake was teaching the client to use Named Ranges (grin!)


    (later)
    I found this in Excel 2002
    About linking to another workbook or program
    (snip!)
    Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!), and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xls. (with no mention of the square brackets)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel links square brackets (Excel 2003)

    The square brackets are used to separate the workbook name from the worksheet name.
    If you refer to a named range that is global to the workbook, you don't need to specify the worksheet name, only the workbook name and the range name. Since these are already separated by an exclamation mark !, you don't need square brackets around the workbook name.
    If, however, you refer to a named range that is local to a worksheet, you do need to refer to the worksheet name as well as the workbook name, so you'll need square brackets around the workbook name again, just as when you refer to a cell address.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel links square brackets (Excel 2003)

    Thanks.
    I found some code, cloned it, <pre>Public Function ReplaceLink(wbk As Workbook, strNewFile As String)
    Items = 0 'initialize these names
    Times = 0
    Link_Array = wbk.LinkSources 'find all document links

    Items = UBound(Link_Array) 'count the number of links
    For Times = 1 To Items
    Dim strOldFile As String
    strOldFile = Link_Array(Items)
    wbk.ChangeLink strOldFile, strNewFile
    Next Times
    End Function

    Sub TESTReplaceLink()
    Call ReplaceLink(ActiveWorkbook, "C:Blotter20061106GLW.RAW")
    End Sub
    </pre>


    Seems to take all the fun out of parsing strings .... (sad, but happy)

Posting Permissions

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