使用vba excel使用.formula

Question

我正在尝试链接到工作簿。然后删除该链接。该公式在给定完整路径时运行良好，但在传递字符串时失败。在下面的vba中，我尝试从Sheet1的单元格值中给出文件位置的名称。

'Location of Template and Country
    Cntryloc = """" & Sheet1.Range("B5") & """"
    Debug.Print Cntryloc
    TempLoc = "" & Sheet1.Range("B11") & ""
    Finaltemplloc = Sheet1.Range("B17")

    i=2

   'Getting the name of excel Sheet
   CntryExcel = Sheet1.Range("C5")

   TempLoc = "" & Sheet1.Range("B11") & ""
   Workbooks.Open TempLoc & "\" & "Bank" & ".xlsx", True, False
   Workbooks("" & FName & ".xlsx").Activate
   ActiveWorkbook.Unprotect Password:="Tall.Trees"
   Worksheets("Template").Unprotect Password:="Tall.Trees"
   Worksheets("Template").Range("D14").Formula = "='&"["&CntryExcel&"]Dump"&"'"&"!"&"$A$" & i""
   ActiveWorkbook.BreakLink Name:=Cntryloc, Type:=xlExcelLinks
         Worksheets("Template").Protect Password:="Tall.Trees"
     ActiveWorkbook.Protect Password:="Tall.Trees"

    'Location for Final Output

    ActiveWorkbook.SaveAs Filename:=Finaltemplloc & "\" & Bank.xlsx
    ActiveWorkbook.Close

Answer 1

试试这个

Worksheets("Template").Range("D14").Value = "='[" & CntryExcel & "]Dump'!" & "$A$" & i & ActiveWorkbook.BreakLink & "Name:=" & Cntryloc & ", Type:=" & xlExcelLink

Answer 2

尝尝这个

Worksheets("Template").Range("D14").Formula = "='[" & CntryExcel & "]Dump!$A$" & "i"

这应该可以修复公式输入

但是，根据您在初始化之前的注释('Getting the name of excel Sheet)，检查CntryExcel是否包含工作簿名称，而不是工作表名称