vba 防止每次修改带有链接的单元格时打开“更新值：”对话框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26722693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent the "Update Values:" dialog box from opening every time a cell with a link is modified
提问by user2722188
Quick version: I've got broken links in a file I'm working with because they're pointing to someone else's hard drive. A macro went wrong in someone else's file that converted all formulas to text by appending an apostrophe before the formula. I wrote a macro to fix this, but there are a ton of external links in the file. The macro essentially changes a formula from the first line to the second line below, doing nothing more than removing the unnecessary apostrophe.
快速版本：我正在处理的文件中的链接已损坏，因为它们指向其他人的硬盘驱动器。其他人的文件中的宏出错了，该文件通过在公式前附加撇号将所有公式转换为文本。我写了一个宏来解决这个问题，但文件中有大量的外部链接。该宏实质上将公式从第一行更改为下面的第二行，只是删除了不必要的撇号。
1) '='C:\OtherPersonsFolderPath\[FileName.xlsm]Sheet1'!A1
2) ='C:\OtherPersonsFolderPath\[FileName.xlsm]Sheet1'!A1
If I do this manually, Excel opens a dialog box asking me to "Update Values" in FileName.xlsm by pointing to the right file. I don't want to update the file path though: I plan to give this back to the original owner of the file with all paths in tact, sans apostrophes. If I hit the "cancel" button on that dialog box, I get the intended effect: The formula updates to what I need, and the value changes to whatever it used to be back when it was a working link. It works fine if I manually hit "cancel" on the box every time it pops up, but I've got thousands of cells to iterate through across dozens of sheets. I need a way to tell VBA to say "cancel" in that box, or prevent the box from appearing in the first place. Any ideas? My code is below:
如果我手动执行此操作，Excel 会打开一个对话框，要求我通过指向正确的文件来“更新 FileName.xlsm 中的值”。不过，我不想更新文件路径：我计划将其返回给文件的原始所有者，所有路径都完好无损，没有撇号。如果我点击该对话框上的“取消”按钮，我会得到预期的效果：公式更新为我需要的内容，并且值更改为它曾经是工作链接时返回的任何内容。如果我每次弹出框时手动点击“取消”，它就可以正常工作，但是我有数千个单元格要在几十张纸上迭代。我需要一种方法来告诉 VBA 在该框中说“取消”，或者首先阻止该框出现。有任何想法吗？我的代码如下：
Public Sub MyBugFix()
Application.Calculation = xlCalculationManual
'Note that I unsuccessfully tried options like "ThisWorkbook.UpdateLinks = xlUpdateLinksNever" and "Application.DisplayAlerts = False" here
Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
Sheets(I).Visible = True
Sheets(I).Select
Range("A1:BZ400").Select
'Simple fix for embedded apostrophes in formulas (e.g., an equals sign in an IF statement)
Selection.Replace What:="'=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'More complex fix for apostrophes at the start (they're special characters, so the find/replace trick doesn't work)
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error Resume Next
For Each myCell In myRng.Cells
If myCell.PrefixCharacter <> "" Then
myCell.Value = "" & myCell.Text
On Error Resume Next
End If
Next myCell
Next I
Application.Calculation = xlCalculationAutomatic
End Sub
回答by Loophole
I found a solution here: http://www.mrexcel.com/forum/excel-questions/506273-turn-off-update-values-dialog-box.htmlso I can't claim any credit for it!
我在这里找到了一个解决方案：http: //www.mrexcel.com/forum/excel-questions/506273-turn-off-update-values-dialog-box.html所以我不能声称它有任何功劳！
Put this before you edit the formula ...
在编辑公式之前把它放在...
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
Then turn it back on after you have made your edits...
然后在您进行编辑后重新打开它...
ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
This solved a similar problem for me, where I was using VBA to write cell formula containing references to other spreadsheets. All credit goes to AlphaFrog on the MrExcel forum!
这为我解决了一个类似的问题，我使用 VBA 编写包含对其他电子表格的引用的单元格公式。所有功劳都归于 MrExcel 论坛上的 AlphaFrog！
回答by zerala
I found out a combination of commands:
我发现了一个命令组合：
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
Application.DisplayAlerts = False
'your macro
'你的宏
ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True
Best Regards
此致
回答by zerala
Disabling the Select Sheet or Update Values dialog:
禁用“选择工作表”或“更新值”对话框：
????????????????
??????????????????
This short macro's use of the Range.TextToColumns methodshould quickly sweep away any prefixing tick (aka ', single-quote or Chr(39)) .PrefixCharactersfrom an entire workbook. While the operation is intended to target formulas that have been commented out of operation with a ', it will also revert text that looks like numbers to true numbers.
这个简短的宏使用Range.TextToColumns 方法应该可以快速清除整个工作簿中的任何前缀刻度（又名'，单引号或Chr(39)）.PrefixCharacters。虽然该操作旨在针对已被注释掉的公式'，但它也会将看起来像数字的文本恢复为真正的数字。
Sub Clean_Prefix_Ticks()
Dim w As Long, c As Long
With ActiveWorkbook '<- set this workbook properly if necessary
For w = 1 To Worksheets.Count
With Worksheets(w).UsedRange
For c = 1 To .Columns.Count
With .Columns(c)
If CBool(Application.CountA(.Cells)) Then _
.Cells.TextToColumns Destination:=.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End With
Next c
End With
Next w
End With
End Sub
The bulk nature of removing the Range.PrefixCharacter propertyallows the Text-to-Columns operation to sweep through each worksheet's .UsedRangewithout raising the external reference Select Sheetdialog.
删除Range.PrefixCharacter 属性的批量性质允许 Text-to-Columns 操作扫描每个工作表的.UsedRange，而无需引发外部参考选择工作表对话框。
If there was sufficient need, each column could easily be examined before the operation took place. The above code was written to handle a workbook-wide sweep.
如果有足够的需要，可以在操作开始前轻松检查每根色谱柱。上面的代码是为了处理工作簿范围的扫描而编写的。
Caveat: The TextFileColumnDataTypes propertyis set to xlGeneralFormat. Be advised that you may lose some forms of special cell formatting that you have assigned; especially text formatting on characters within a string (e.g. Range .Characters property).
警告：TextFileColumnDataTypes 属性设置为xlGeneralFormat。请注意，您可能会丢失某些已分配的特殊单元格格式；尤其是字符串中字符的文本格式（例如Range .Characters 属性）。
