Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    If find .xls update links else and update link from .xlsx

    Hi All,

    I need a code that look in the specific folder if find test.xls then update links in the master.xlsm from test.xls other wise update links from test.xlsx?

    Any help would be highly appreciated...

    Regards,
    hammeed

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand what you want. You will need to be much more specific. Is this a followup to the thread you started at http://windowssecrets.com/forums/sho...-.xls-and-xlsx?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Arrow

    HI steve,
    Yes that is the follow up of mentioned post on that time you have solved the issue, the problem is for me i always update the link. It is possible that it update links automatically if find test.xls in the folder then update links for test.xls else if find excel file test.xlsx in the same folder update links from test.xlsx

    regards,
    farrukh

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think the easiest thing to do would be to train people not to change file names....

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can try this code...
    Steve

    Code:
    Option Explicit
    Sub RepairHyperlinks()
      Dim wks As Worksheet
      Dim hl As Hyperlink
      Dim x As Integer
      On Error GoTo ErrHandler
      For Each wks In ActiveWorkbook.Worksheets
        For Each hl In wks.Hyperlinks
          x = 0
          hl.Follow
          ActiveWorkbook.Close
        Next
      Next
      Exit Sub
    ErrHandler:
      If Err.Number = -2147221014 Then
        x = x + 1
        If Right(UCase(hl.Address), 4) = ".XLS" Then
          hl.Address = hl.Address & "x"
        ElseIf Right(UCase(hl.Address), 5) = ".XLSX" Then
          hl.Address = Left(hl.Address, Len(hl.Address) - 1)
        End If
        If x > 2 Then
          MsgBox hl.Address & " in " & hl.Parent.Address & " could not be fixed"
          Exit Sub
        Else
          Resume
        End If
      Else
        MsgBox Err.Number & vbCrLf & Err.Description
      End If
    End Sub

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-08-21)

  7. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir steve,

    When i run this code in my master.xlsm file it does not do any thing...

    The sample is atttached with in which i attached both test.xls and test.xlsx, when i delete test.xls from sample folder and run the code to update values from test.xlsx file the code did not do any thing please guide.

    Thanks
    hammeed
    Attached Files Attached Files

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The code fixes hyperlinks, the file you attached does not have hyperlinks. If it is just formulas that are the issue, normal find/replace should be able to fix it or even use the "edit links" dialog to change the source...

    As mentioned before, if you have this file open with the linked files and then save that file with a new name, the links will automatically be updated...

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-08-21)

  10. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Thumbs up

    Sir steve,

    Thank you so much this valuable help has solved my issue.

    Regards,
    hammeed

Posting Permissions

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