Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 35

Thread: VBA Edit Links

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location

    VBA Edit Links

    Hiya,

    Need some help on my coding below.

    I'm trying to edit the links when the file copies over the formulas and change the source book from the xlsm file to the xlsx. I've attempted the coding but it has no impact when I play through the code, which would indicate that I'm missing something or its not in the right order, but I simply can't figure out where I'm going wrong. Any ideas?

    I've highlighted the code where I want to edit the links

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As RangeDim mypath As String, myfile As String, scc As String, sto As String
    Dim link, linkSources, newLink As String
    mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting"
    With Sheets("Control")
     sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
      End With
    Set w = Sheets("Mainstay Master")
    Set X = Sheets("Mainstay Report")
    Set b = Workbooks.Add
    Set Rng = b.Sheets(1).Range("F4:F13")
    
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    
    With w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy
        b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
    X.Copy , b.Sheets(1)
    b.Sheets(1).Name = "Mainstay Master"
    
    Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
    X.Range("b5").PasteSpecial Paste:=xlPasteValues
    
    newLink = ThisWorkbook.Path & "\Mainstay Master.xlsx"
    linkSources = ThisWorkbook.linkSources(xlLinkTypeExcelinks)
    If IsArray(linkSources) Then
            For Each link In linkSources
                If InStr(link, "Mainstay Master Template.xlsm") Then _
              ThisWookbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
         Next
         End If
    
    With X
        Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sitenames"
         End With
    Last edited by Paul_Hossler; 02-24-2021 at 04:50 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Welcome to the forum - please take a minute to read the FAQs at the link in my sig

    I added CODE tags to your post - you can use the [#] icon to enter them and paste your macro between
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Thanks Paul.

    Do we always apply the code tags when posting the code?

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by SteveABC View Post
    Thanks Paul.

    Do we always apply the code tags when posting the code?
    Yes, it sets of the macro and applies some formatting


    Sub NoCODEtags()
    Msgbox "Hello World"
    End Sub

    Sub WithCODEtags()
        Msgbox "Hello World"
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In my version of Excel (O365) the type of linksource for a link to an Excel workbook is xlExcelLinks, not xlLinkTypeExcelLinks
    linkSources = ThisWorkbook.linkSources(xlExcelLinks)
    You could use the number 1 instead:
    linkSources = ThisWorkbook.linkSources(1)
    What version of Excel are you using?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Cross posted elsewhere.

  7. #7
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    I've changed the code as below but still didn't work. I'm using O365. Any other ideas?


    You could use the number 1 instead:
    linkSources = ThisWorkbook.linkSources(1)
    What version of Excel are you using
    Last edited by SteveABC; 02-26-2021 at 06:31 AM. Reason: add tags

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    direrctly after the line which begins:
    newLink = ThisWorkbook.....
    try adding the temporary line:
    MsgBox IsArray(ThisWorkbook.LinkSources)
    and run it.
    What does the messagebox say?

    Where is this code? (Standard code module? a sheet's code-module? the ThisWorkbook code-module) and in which workbook is it situated? (Let's hope it's not within the Personal workbook.)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You need to say where you've crossposted this question.

  10. #10
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Message box stated = True.

    The code is situated in the workbook....Mainstay Master Template.xlsm


    Quote Originally Posted by p45cal View Post
    direrctly after the line which begins:
    newLink = ThisWorkbook.....
    try adding the temporary line:
    MsgBox IsArray(ThisWorkbook.LinkSources)
    and run it.
    What does the messagebox say?

    Where is this code? (Standard code module? a sheet's code-module? the ThisWorkbook code-module) and in which workbook is it situated? (Let's hope it's not within the Personal workbook.)

  11. #11
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Why? Can delete, its not a problem.


    Quote Originally Posted by p45cal View Post
    You need to say where you've crossposted this question.

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by SteveABC View Post
    Message box stated = True.

    The code is situated in the workbook....Mainstay Master Template.xlsm
    It's so difficult to do this in the dark, but check very carefully where you're adding/changing things and whether it's happening where you think it's happening:
    All references to ThisWorkbook in the code refer to the workbook where the code resides, which you tell me is Mainstay Master Template.xlsm
    So lines such as:
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    where you're adding a name to Mainstay Master Template.xlsm, not to any newly created workbook.
    This line:
    linkSources = ThisWorkbook.linkSources(1)
    will return the links from Mainstay Master Template.xlsm
    and this:
    ThisWorkbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
    will try to update links in Mainstay Master Template.xlsm

    Is that what you want to happen?
    Perhaps those references to ThisWorkbook need to be changed to b?
    Don't forget to correct both instances of xlLinkTypeExcelLinks to 1 or xlExcelLinks.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by SteveABC View Post
    Why? Can delete, its not a problem.
    There's no need to delete.
    This will give you a full explanation: http://www.excelguru.ca/content.php?184

  14. #14
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    I've added this post to Mr ExcelForum, but I'm going to delete as no response.

    Quote Originally Posted by p45cal View Post
    There's no need to delete.
    This will give you a full explanation:

  15. #15
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Hello.

    Yes I can see why its hard to resolve if I don't make it clear for you. So let me give you some more information.

    Basically I've created a new workbook which I've created two tabs and copied over data from my main file called "Mainstay Master Template.Xlsm"

    W = Mainstay Master
    X = Mainstay Report.

    In my main file. The Mainstay report (x) gets its information from the Mainstay master tab (w). Which is essentially what I want to reproduce in the new workbook.

    When I copy over the formula's however. The mainstay report still has references to the Mainstay Master Template.xlsm. So I want the formula's in Mainstay report (x) to only reference the Mainstay master (W) tab in the new created workbook. So no links to the Mainstay Master template.xlsm file.

    Think I've changed the code in red as per your last post, but not sure If I've totally changed as you suggested. It breaks on this line.

    linkSources = b.Sheets(1).linkSources

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
    Dim mypath As String, myfile As String, scc As String, sto As String
    Dim link, linkSources, newLink As String
    mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
    With Sheets("Control")
     sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
      End With
    Set w = Sheets("Mainstay Master")
    Set x = Sheets("Mainstay Report")
    Set b = Workbooks.Add
    Set Rng = b.Sheets(1).Range("F4:F13")
    Set linkSources = b.Sheets(1)
    
    
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    
    
    With w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy
        b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
    x.Copy , b.Sheets(1)
    b.Sheets(1).Name = "Mainstay Master"
    
    
    Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
    x.Range("b5").PasteSpecial Paste:=xlPasteValues
    
    
    With x
        Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sitenames"
         End With
         
    newLink = mypath & "\Mainstay Master.xlsx"
    linkSources = b.Sheets(1).linkSources
    If IsArray(linkSources) Then
            For Each link In linkSources
                If InStr(link, "Mainstay Master.xlsx") Then _
              x.Sheets.ChangeLink link, newLink, xlExcelLinks
         Next
         End If

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    give these a try:
    linksources=b.linksources(1)
    b.ChangeLink link, newLink, 1
    I don't think you need the line further up the macro:
    Set linkSources = b.Sheets(1)
    You might also need to change (if you have problems):
    newLink = mypath & "\Mainstay Master.xlsx"
    to
    newLink = mypath & "Mainstay Master.xlsx"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Thank you for coming back promptly on this.

    Unfortunately the suggestions you made didn't work.

    I'm still seeing the reference as per the below.

    =VLOOKUP($B$4,'[Mainstay Master Template.xlsm]Mainstay Master'!$F:$BU,27,FALSE)

    Here's what the codes looks like now.

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
    Dim mypath As String, myfile As String, scc As String, sto As String
    Dim link, linkSources, newLink As String
    mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
    With Sheets("Control")
     sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
      End With
    Set w = Sheets("Mainstay Master")
    Set x = Sheets("Mainstay Report")
    Set b = Workbooks.Add
    Set Rng = b.Sheets(1).Range("F4:F13")
    
    
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    
    
    With w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy
        b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
    x.Copy , b.Sheets(1)
    b.Sheets(1).Name = "Mainstay Master"
    
    
    Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
    x.Range("b5").PasteSpecial Paste:=xlPasteValues
    
    
    With x
        Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sitenames"
         End With
         
    newLink = mypath & "\Mainstay Master.xlsx"
    linkSources = b.linkSources(1)
    If IsArray(linkSources) Then
            For Each link In linkSources
                If InStr(link, "Mainstay Master.xlsx") Then _
              b.ChangeLink link, newLink, 1
         Next
         End If

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    This is going to have to be done trial and error.
    Your code:
    If InStr(link, "Mainstay Master.xlsx") Then _
    looks for .xlsx, shouldn't it be looking for xlsm?
    Or more completely: Mainstay Master Template.xlsm ?

    I suspect, but not sure, that your:
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    should be:
    b.Names.Add Name:="Sitenames", RefersTo:=Rng
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    I've changed the below as you specified...but now its asking to be manually pointed. Which I'm guessing is good and we're heading on the right path.

    This is going to have to be done trial and error.
    If InStr(link, "Mainstay Master.xlsx") Then _
    
    looks for .xlsx, shouldn't it be looking for xlsm?
    Or more completely: Mainstay Master Template.xlsm ?


    I've changed from thisworkbook to B.names.

    but the code now breaks on this line... with application or object defined error. Is this because of the Sitenames reference?

        Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sitenames"
    I suspect, but not sure, that your:
    
    
    ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng
    should be:
    b.Names.Add Name:="Sitenames", RefersTo:=Rng
    [/QUOTE]

  20. #20
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    With these two lines:
    Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
    x.Range("b5").PasteSpecial Paste:=xlPasteValues
    you're copying from Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report") to x, x which is:
    Set X = Sheets("Mainstay Report") of the active workbook before you added another workbook; that is the same sheet. So aren't you just converting a formula in cell B5 on that sheet to a plain value? Maybe that's the intention.

    With this next bit of code:
    With x
        Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sitenames"
         End With
    although you've got With x, you're not using it; Range("B4") is not a qualified range so the active sheet is assumed (unless this code is in a sheet's code-module instead of a standard code-module). I don't know which sheet it's referring to, nor which sheet you want to add the validation to.

    This is so difficult to do in the dark.
    If you want we could do a TeamViewer session tomorrow sometime (I'm in the UK).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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