+ Reply to Thread
Results 1 to 6 of 6

Unlink Saved Copy of Original Workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Unlink Saved Copy of Original Workbook

    The code below creates a copy of the original workbook but only contains specific sheets. The saved name is created from the code but allows the user to select what folder and path they prefer. The saved copy contains links that requires the user when opening the copy to select update or ignore. I would like to remove any links when its saved and eliminate this popup when users open the file. I tried inerting the ListObject.Unlink command, but no luck. Can someone help me with this?

    Sub NewBook()
    '
    ' NewBook Macro
    '
     
        Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
            "AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area")).Select
        Sheets("By Location").Activate
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
            "AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Select
        Sheets("By Location").Activate
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
            "AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Select
        Sheets("Terms").Activate
    
        Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
            "AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Copy
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        ActiveWindow.ScrollWorkbookTabs Sheets:=1
        
     Dim IntialName As String
     Dim sFileSaveName As Variant
     Dim dt As String
     InitialName = "AR COMBINED REPORT "
     dt = VBA.format((Date), "mm-dd-yyyy")
        
        
    '    ActiveWorkbook.SaveAs fileName:= _
    '        "Y:\Accounts Receivable Reports - Combined\Book14.xlsx", FileFormat:= _
    '        xlOpenXMLWorkbook, CreateBackup:=False
            
    
     sFileSaveName = Application.GetSaveAsFilename(InitialName & dt, "Excel Workbook (*.xlsx), *.xlsx")
         If sFileSaveName <> False Then
             ActiveWorkbook.SaveAs sFileSaveName
         End If
            
         
    'ungroup sheets in original
        Windows("AR COMBINED REPORT MACRO TEMPLATEv5.0.xlsm").Activate
        Sheets("AR over 270 Days").Select
    End Sub

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Unlink Saved Copy of Original Workbook

    Include this bit of code before saving the copied file.

    I'm assuming you have earlier already saved the original workbook

    Dim arLinks As Variant, x As Long
      arLinks = ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
      For x = 1 To UBound(arLinks)
        ThisWorkbook.BreakLink Name:=arLinks(x), Type:=xlLinkTypeExcelLinks
      Next x
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Unlink Saved Copy of Original Workbook

    Thanks for the code. I tried inserting it at various locations and always get a type mismatch error on the For x=1 to Ubound(arLinks) line. The arLLinks variable always shows as empty. I don't think it's picking up the workbook? Do I need to set a dimension for the workbook?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Unlink Saved Copy of Original Workbook

    Does the original contain the links too?

    The code needs to be added before you choose to do any .Copy since once you do this the new workbook becomes the ActiveWorkbook and the ThisWorkbook still refers to the original.

    Alternatively if you've already done the .Copy and the copied workbook is active use instead

    ActiveWorkbook.BreakLink Name:=arLinks(x), Type:=xlLinkTypeExcelLinks

  5. #5
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Unlink Saved Copy of Original Workbook

    Hi Richard
    Yes the original workbook contains links that i dont want to break. I just want to break links in the copy back to the original. The copy is is used throuout the business and while most won;t update , some may ty to. I'll test this new line of code

  6. #6
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Unlink Saved Copy of Original Workbook

    Thank You for the help on this

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Save Copy Of Workbook & Keep Both Open (Original and copy)
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2017, 10:52 AM
  2. Can't Unlink Old Macro Workbook
    By phelbin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2017, 04:06 PM
  3. [SOLVED] Moving between original and saved copy of a workbook
    By Craig Muir in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2014, 07:55 AM
  4. Saved Copies of Workbook Reference Original
    By mattdh12 in forum Excel General
    Replies: 1
    Last Post: 04-10-2014, 03:25 AM
  5. Copy graph to new workbook while leaving data in original workbook
    By smjpl in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-10-2012, 12:49 PM
  6. Replies: 1
    Last Post: 04-01-2006, 03:50 PM
  7. [SOLVED] How can I see a copy of a saved workbook before I saved it again?
    By Norma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2005, 06:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1