如何从工作簿中删除链接到另一个的链接 [英] How do I remove links from a workbook linked to another
问题描述
我在工作簿(评分)中有一个工作表(问题),在问题"工作表的底部有一个按钮,用于复制评分"工作簿中的工作表2(引用)并将其粘贴到新的工作簿中,该工作簿根据报价编号,然后保存.
I have a sheet(Questions) in a workbook(Rating) that has a button at the bottom of the Questions sheet that copies sheet 2(quote) from the Rating workbook and pastes it in a new workbook that is named according to the quote number and then saved.
这是代码:
Sub GetQuote()
Range("AK548").Select
Selection.Copy
Range("AK549").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim Output As Workbook
Dim FileName As String
Set Output = Workbooks.Add
FileName = ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("Questions").Range("AK545").Value & ".xls"
Output.SaveAs FileName
Application.DisplayAlerts = False
Output.Worksheets("Sheet1").Delete
ThisWorkbook.Worksheets(2).Copy Before:=Output.Worksheets("Sheet2")
Output.Worksheets(1).Name = "Sheet1"
Application.DisplayAlerts = True
Output.Protect Password:="12345"
Output.Save
End Sub
现在,我打算删除此新副本和报价单之间现在存在的链接,只保留这些值.我该怎么做?
Now I intend on removing the links that now exsist between this new copy and the Quote sheet and only leave the values. How would I do this?
我发现此代码应删除现有的链接:
I have found this code that should delete the links that exsist:
Dim Cell As Range, FirstAddress As String, Temp As String
'delete all links from selected cells
Application.ScreenUpdating = False
With Selection
Set Cell = .Find("=*!", LookIn:=xlFormulas, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error GoTo Finish
FirstAddress = Cell.Address
Do
Temp = Cell
Cell.ClearContents
Cell = Temp
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End With
Finish:
我所做的全部工作就是将这段代码放在命名并复制工作表"的代码下面,而这行不通?
All I have done extra is put this code in below the code that Names and copies the sheet and that did not work?
那么,现在我该如何结合这两段代码,以便复制所有内容并删除链接?
So now how would I combine these two pieces of code so that everything gets copied and the links removed?
推荐答案
我有现有的工作簿,这些工作簿具有需要从工作簿中删除然后重新保存的外部链接.
I had existing workbooks that had external links that i needed to remove from the workbooks and then re save them.
这对我有用:
Sub BreakExternalLinks()
'PURPOSE: Breaks all external links that would show up in Excel's "Edit Links" Dialog Box
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim ExternalLinksArray As Variant
Dim wb As Workbook
Dim x As Long
Set wb = ActiveWorkbook
'Create an Array of all External Links stored in Workbook
ExternalLinksArray = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
'if the array is not empty the loop Through each External Link in ActiveWorkbook and Break it
If IsEmpty(ExternalLinksArray) = False then
For x = 1 To UBound(ExternalLinksArray )
wb.BreakLink Name:=ExternalLinksArray (x), Type:=xlLinkTypeExcelLinks
Next x
end if
End Sub
这篇关于如何从工作簿中删除链接到另一个的链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!