vba - Excel VBA : Formula Not Entering Correctly From String

Question

我正在尝试完成一个脚本，该脚本允许用户在双击单元格时选择另一个 excel 文件，然后该 excel 文件用于将公式放入主 excel 文件中。

我不能单独使用单元格值，因为需要在脚本完成时在编辑栏中看到文件路径。所以问题是输入的公式与它应该从中提取的字符串文本不匹配。

为了澄清起见，我使用的名为 FormulaPath 的字符串最终是一个以“...\00975-006-00[00975-006-00.xls]QuoteDetails'!”结尾的公式，这将是正确的公式。

但是当我用它把公式输入一个范围时:

Range("A1").Formula = "=" & FormulaPath & "$C$100"

实际公式最终输入为 "...[00975-006-00[00975-006-00.xls]Quote Details]00975-006-00[00975-006-00.xls]Q'!$C$100
注意到重复了吗？

我现在在手机上，如果格式古怪，请原谅我。完整脚本如下。谢谢!

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ImportWB, QuoteWB As Workbook
Dim AdInsWS, AdInsCostWS As Worksheet
Dim ImportPathName As Variant
Dim FormulaPath As String

Set QuoteWB = ThisWorkbook
Set AdInsWS = QuoteWB.Sheets("Ad-Ins")
Set AdInsCostWS = QuoteWB.Sheets("Ad-ins cost")

  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    'set default directory
    ChDrive "Y:"
    ChDir "Y:\Engineering Management\Manufacturing Sheet Metal\Quotes"
     'open workbook selection
       ImportPathName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Please select a file")

     If ImportPathName = False Then 'if no workbook selected
          MsgBox "No file selected."

        ElseIf ImportPathName = ThisWorkbook.Path & "\" & ThisWorkbook.Name Then 'if quote builder workbook selected
            MsgBox "Current quote workbook selected, cannot open."

        Else
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
                  Workbooks.Open Filename:=ImportPathName, UpdateLinks:=False

                    Set ImportWB = ActiveWorkbook
                    FormulaPath = "'" & ImportWB.Path & "[" & ImportWB.Name & "]Quote Details'!"
                     AdInsCostWS.Range("B3").Formula = "=" & FormulaPath & "$C$100"
                     ImportWB.Close
     End If
        Cancel = True
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
   End If
End Sub

Answer 1

我只需在 FormulaPath 字符串中添加一个反斜杠，就可以使您的脚本正常工作:

FormulaPath = "'" & ImportWB.Path & "\[" & ImportWB.Name & "]Quote Details'!"