vba - 使用 VBA 将公式分配给 Excel 单元格

Question

如何将公式(从位于网络驱动器上的另一个文件中读取数据而不打开它)分配给同一驱动器上不同文件夹中的另一个 Excel 文件中的单元格？

最终用户拥有网络驱动器上所有文件夹的访问权限，因此不需要用户名和密码。

我是 VBA 新手，尝试使用以下代码，但系统提示我查找并打开数据文件:

Dim Network_Path As String

Network_Path = "\\MyNetworkDrive\folder\subfolder"

With ThisWorkbook.Sheets("Sheet1")
    .Range("C2").Formula = "=SUM('Network_Path\[Source_File.xlsx]Data'!$B$2:$B$20)"
End With

Answer 1

尝试将 NetworkPath 写为公式中的变量:

.Range("C2").Formula = "=SUM(" & Network_Path  &  "\[Source_File.xlsx]Data'!$B$2:$B$20)"

<小时/>

关于带变量的公式的一般调试，请尝试以下简单技巧:

Public Sub TestMe()
    Dim np              As String 'do not use "_" in file name FWIW
    np = "C:\Users\"      'or something else
    Debug.Print "=IFERROR(INDEX('" & np & "[Source_File.xlsx]Data'!A:A; MATCH('Sheet1'!C3;'" & np & "[Source_File.xlsx]Data'!R:R; 0)); "")"
End Sub

然后查看立即窗口中显示的内容。按 Ctrl+G 将其打开。当前公式的结果是:

=IFERROR(INDEX('C:\Users\[Source_File.xlsx]Data'!A:A; MATCH('Sheet1'!C3;'C:\Users\[Source_File.xlsx]Data'!R:R; 0)); ")

因此，您可能必须找到一种方法来删除最后一个 " 符号:

然后尝试将公式复制到 Excel 文件中，如果有效，则表示没问题。

<小时/>

另一种选择是尝试做完全相反的事情。例如，按照应有的方式在 Excel 中编写公式并确保其有效。然后选择包含公式的单元格并运行以下命令:

Public Sub PrintMeUsefulFormula()

    Dim myFormula  As String
    Dim myParenth  As String

    myParenth = """"

    myFormula = Selection.Formula
    myFormula = Replace(myFormula, """", """""")

    myFormula = myParenth & myFormula & myParenth
    Debug.Print myFormula

End Sub

它将在立即窗口中打印公式，因为它应该在 VBA 中使用。然后用变量替换路径并尝试一下。