如何将关闭工作簿的范围引用到数据验证

Question

如何通过VBA代码添加将引用另一个CLOSED工作簿范围的验证列表？ 我不能这样做Formula1:="=INDEX('C:...)"？

我可以通过以下方式管理：

With ThisWorkbook.Sheets("sertifika").Range("Ab63:Ab100").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, _
Formula1:=Join(checkref, ",")

但是对于长字符串值 files.xlsm 文件将在保存文件后损坏。

然后我尝试了：

With ThisWorkbook.Sheets("T").Range("K10:K100").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, 
Operator:=xlBetween, _
Formula1:="=INDEX('C:[D.xls]Lists'!$D$2:$D$10,,1)"

Return error 1004

Answer 1

根据Microsoft的文档，不能将外部引用与数据验证一起使用。 您可以在以下链接中找到文档...

https://learn.microsoft.com/en-us/office/troubleshoot/excel/external-references-data-validation-fails

作为解决方法，您可以使用 ExecuteExcel4Macro 方法访问已关闭工作簿中的值。 您可以在以下链接中找到文档...

https://learn.microsoft.com/en-us/office/vba/api/excel.application.executeexcel4macro

使用此方法，将访问值以构建字符串，然后将其用作验证的源。但请注意，有 255 个字符的限制。

此外，这意味着您需要在每次打开工作簿时更新数据验证，以确保您具有最新的值。 若要自动执行此操作，可以使用工作簿打开事件过程。

---

解决方法

[常规模块]

以下宏应放置在常规模块中，并将添加来自外部源的数据验证...

Option Explicit
Sub UpdateDataValidation()
Dim sourcePath As String
sourcePath = "C:UsersDomenicDesktop" 'change the path accordingly
Dim sourceFileName As String
sourceFileName = "Sample.xlsm" 'change the file name accordingly
Dim sourceSheetName As String
sourceSheetName = "Sheet1" 'change the sheet name accordingly
Dim sourceReference As String
sourceReference = "D2:D10" 'change the reference accordingly
Dim currentCell As Range
Dim currentValue As String
Dim validationList As String
validationList = ""
For Each currentCell In Worksheets(1).Range(sourceReference) 'any worksheet reference will do for our purposes here
currentValue = ExecuteExcel4Macro("'" & sourcePath & "[" & sourceFileName & "]" & sourceSheetName & "'!" & currentCell.Address(, , xlR1C1))
validationList = validationList & "," & currentValue
Next currentCell
validationList = Mid(validationList, 2)
With ThisWorkbook.Sheets("Sheet1").Range("A10:A100").Validation 'change the sheet name and range accordingly
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=validationList
End With
End Sub

[此工作簿模块]

以下宏应放置在 ThisWorkbook 的代码模块中，并且每当打开工作簿时都会自动更新数据验证的值...

Option Explicit
Private Sub Workbook_Open()
UpdateDataValidation
End Sub

Answer 2

您可以使用 Excel 数据模型在没有宏的情况下执行此操作。首先将下拉选项放在源工作簿中，并命名该范围(使用公式 --> 名称管理器)。关闭此工作簿。在主工作簿中，转到"数据"-->"从文件获取数据"-->>"从Excel"工作簿。然后选择源工作簿(需要关闭)并单击导入。从列表中选择命名区域。单击加载。然后单击主工作簿中加载的数据，单击"数据"--">查询和连接"--">属性"，然后单击查询名称旁边的查询属性图标(带有两个正方形的矩形)。将数据设置为在打开文件时刷新，并每 5 分钟刷新一次。然后，您可以使用主工作簿中数据的此副本作为数据验证列表的源。