Macro to break links when saving to a new file

W

wsneeden

New Member
Joined
Mar 4, 2008
Messages
1
Hopefully someone out there can help with a macro problem I am having in '07.

The macro opens a workbook and the workbooks that it is linked to so the form can udate. After the update a copy is saved with the new information which can then be viewed by all users and stored for archive reasons. A new file is created each time that the macro is run so this is not overwritting the same file over and over again.

The problem is that the saved workbook still contains all of the links that were in the original file. Does anyone know a way that I can save the file as values only without the links?


Application.Visible = False
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\Communication Sheet.xlsx", _
UpdateLinks:=3
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs unshipped.xls"
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWorkbook.SaveCopyAs "\\Rpf-baltimore\shared\PRODUCTION\Production Reporting\communication sheet\CS_" & Format(Date, "mmddyy") & "_" & Format(Time, "hhmm") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
ActiveWindow.Close
Application.Quit
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
This should do the trick for you. The below code will break all the links in your workbook leaving the values in the cells.

Code: 
Sub BreakThoseLinks()
     
Dim myLinks As Variant
     
' Define variable as an Excel link.
myLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
     
' The below breaks all the links.
For i = 1 To UBound(myLinks)
    ActiveWorkbook.BreakLink Name:=myLinks(i), Type:=xlLinkTypeExcelLinks
Next i

End Sub

Hope this helps!

Dave
 
Upvote 0
This is a very helpful response Dave. Does anyone know how to put an if statement into this for if I rename this file and go to save it again so it won't debug because there aren't any links to break?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
136
joxyin
J
M
  • Solved
Open file macro failing
Replies
3
Views
286
Joe4
Joe4
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
244
Peter_SSs
Peter_SSs
J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
139
bobsan42
bobsan42
M
  • Solved
PDF's won't open in Sharepoint/Teams but will in C:drive Documents folder
Replies
3
Views
699
Mr_Ragweed2
M

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top