Trying to remove links when save as a new workbook

[Kestrel Aviation]

Hi All,

I'm still struggling along with my Quote spreadsheet. Very much a newbie to VBA, but trying....

My spreadsheet pulls last row data from a register to fill fields for a new quote (Date, Company, Quote#) it's a bit of a hack using LOOKUP, =LOOKUP(2,1/('[Quote Register.xlsm]Register'!$B:$B<>""),('[Quote Register.xlsm]Register'!$B:$B)) etc. for the three fields.

I have some code that should break the links when saved as a new workbook.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? If a previously saved workbook is opened for changes to the quote, the fields get updated with newer register data.

My Code: Has a few snippets which are not necessary? eg: Dim fname As String and fname = ActiveWorkbook.Name
I've been trying to edit existing code on the run and these were part of an old iteration. I did say I was a learner, my day job is flying airplanes ..


Sub SaveQuote()
Dim fname As String
Dim newFile As String
Dim NewBook As Workbook
fname = ActiveWorkbook.Name
ChDir "C:\users\paul\documents\"
Call break_links(NewBook)
ActiveWorkbook.SaveAs Filename:="C:\Users\Paul\Documents\Quote" & " " & Range("D11").Value & " " & Range("K9").Value & (".xlsm")
End Sub



Sub break_links(ByRef wb As Workbook)
Dim Links As Variant
On Error Resume Next
Links = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
On Error GoTo 0
If Not IsEmpty(Links) Then
For I = 1 To UBound(Links)
wb.BreakLink _
Name:=Links(I), _
Type:=xlLinkTypeExcelLinks
Next I
End If
End Sub

Best regards...

[vba_php]

I can provide something for you here. I'm not sure how to interpret your other words, but this:

Hi All,
Does anyone know a way that I can save the file as values only without the links?

that task can be accomplished by simply highlighting the required date, copying it to the clipboard and choosing paste special by right clicking your mouse and choosing values only.

my day job is flying airplanes ..

what kind of planes do you fly? I talked to a flight instructor at a restaurant in a nearby town last week. he has been teaching for 40 years. and I programmed and produced the binary databases that guide planes from takeoff to landing (commercial and private panes) about 8 years ago. that's extremely complex stuff.

[Kestrel Aviation]

The idea is to generate pre populated fields on a workbook called Quote, with Date, Customer and Quote number from the last used row in another workbook called Quote Register. The problem was if the saved quote is later opened the fields get updated with newer data in the Quote Register.

I was looking for a solution to break the links when the workbook was saved as a new workbook with Date, Customer and Quote number as the new filename.

I have found the solution by myself, and learned a little more.


My new code:
For Save As...

Sub SaveQuote()
Dim fname As String
Dim newFile As String
Dim NewBook As Workbook
fname = ActiveWorkbook.Name
Sheet1.Unprotect Password:="redacted"
ChDir "C:\users\paul\documents\"
ActiveWorkbook.SaveAs Filename:="C:\Users\Paul\Documents\Quote" & " " & Range("D11").Value & " " & Range("K9").Value & (".xlsm")
BreakExternalLinks
Sheet1.Protect Password:="redacted", DrawingObjects:=False
End Sub



To Break Links...

Sub BreakExternalLinks()
Dim ExternalLinks As Variant
Dim wb As Workbook
Dim x As Long
Set wb = ActiveWorkbook
ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
End Sub