Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 33

Thread: spreadsheet Links

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Interesting one Yogi, my reading was that he actually wanted the data bringing back though based on what month reference was inputted somewhere in a sheet :

    "What I want to do is bring in the relevant months data into all 31 sheets by simply changing the text entry from JanData.xls to FebData.xls"

    My ugly method achieves it, but I like your lateral thinking : I don't like indirect needing open workbooks, that bothers me for some reason

    cheers
    Chris

  2. #12
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi

    could you show me how you would enter the sheet and Cell reference in C1 of your example

    Thanks

  3. #13
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris
    YES your understanding of what I want is correct.

    I will try your method over the next twenty four hours.

    Keep an eye open for my response

    Many thanks

    Peter

  4. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Peter,

    I took the liberty of assuming you'd just want to type Jan or Feb as the pointer rather than the full filename (as it's just easier to type....)

    if that's a problem, you should be able to amend the formula appropriately, but if can't, shout at me and I'll do it properly


  5. #15
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-11 06:05, Peter100 wrote:
    Hi Yogi

    could you show me how you would enter the sheet and Cell reference in C1 of your example

    Thanks
    Hi Peter:
    The way I used the HYPERLINK function is as follows:
    What I want to get as an argument to the HYPERLINK function is the following:
    "[c:FoldersubFolderFileName.xls]SheetName!CellRef]"

    So, I broke this argument down into thre components ...

    in cell A1 ... [c:FoldersubFolder
    in cell B1 ... FileName.xls]
    in cell C1 ... SheetName!CellRef

    then I rebuilt my formula as

    =HYPERLINK(A1&B1&C1)

    since the file path, and file name will generally remain the same, now I can just change my SheetName!CellRef
    and the Hyperlink is changed accordingly.

    Please post back if it works for you ... otherwise explain a little further and let us take it from there.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #16
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to experiment with a vba procedure, try something like the following. I've added some error testing procedures which have been commentized as if the workbooks that are linked are open, you won't necessarily see the .xls extension. It's a starting point:

    Code:
    Sub Links2()
    'Macro recorded by Nate
    Dim ws As Worksheet, cl As Range, frm As String
    Dim FrmCls As Range, p As String, o As String
    p = InputBox("File name to replace")
    'If Right(p, 3) <> "xls"
    'Then MsgBox "Forgot the extension"
    'end
    'end if
    o = InputBox("New Filename")
    'If Right(o, 3) <> "xls"
    'Then MsgBox "Forgot the extension"
    'end
    'end if
    For Each ws In ActiveWorkbook.Worksheets
        On Error GoTo 1
        Set FrmCls = ws.Range("a1").SpecialCells(xlFormulas)
        For Each cl In FrmCls
            frm = cl.Formula
            If InStr(frm, "[") Then
                cl.Replace What:=p & "]", Replacement:=o & "]", _
                MatchCase:=False
            End If
        Next cl
    1:
        Set FrmCls = Nothing
        Next ws
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-11 12:31 ]

  7. #17
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    CHRIS
    Great that works a treat, except it is copying absolute rather than relatively.

    When copying down the rows, it is alright copying across the columns.
    Go on "make my day" and tell me how

    [ This Message was edited by: Peter100 on 2002-05-13 02:32 ]

    [ This Message was edited by: Peter100 on 2002-05-13 02:41 ]

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This might be a useful macro as well...
    Assuming the filename without the extension is in A1.



    Sub UpdateLink()
    'Before the first run, enter the current filname(without extension)
    'into cell A1 Ex. JanData or FebData, ect...
    Dim sh As Worksheet, NewLink
    NewLink = InputBox("Enter the name of the file to link to your " & _
    "workbook. Do not enter the period or file extension. " & _
    "For example: You might enter FebData to change all of your " & _
    "current links to FebData.xls")
    If NewLink = "" Then Exit Sub
    For Each sh In Worksheets
    sh.Cells.Replace What:=Cells(1, 1), Replacement:=NewLink, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Next
    Cells(1, 1) = NewLink
    End Sub



    Tom

    [ This Message was edited by: TsTom on 2002-05-13 03:03 ]

  9. #19
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HI Tom

    I'm pondering what youv'e said, and will come back on it.
    Chris's way is doing just what I want if only I can get it to copy relatively down the rows.

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Did you remove the $ if any?
    Tom

Some videos you may like

User Tag List

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
  •