Excel VBA: Formula Not Entering Correctly From String

Question

I'm trying to finish a script that will allow a user to select another excel file when a cell is double clicked, then that excel file is used to drop in a formula into the main excel file.

I cannot use the cell values alone because being able to see the file path in the formula bar when the script is complete is required. So the issue is that the formula being entered does not match the string text that it should be pulling from.

For clarification, the string I use called FormulaPath ends up being a formula ending "...\00975-006-00[00975-006-00.xls]QuoteDetails'!" and this would be the correct formula.

But when I use this to enter the formula into a range:

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

The actual formula ends up being entered as "...[00975-006-00[00975-006-00.xls]Quote Details]00975-006-00[00975-006-00.xls]Q'!$C$100

Notice the repetition?

I'm on mobile right now, so forgive me if the formatting is wacky. Full script below. Thanks!

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

brollin brollin · Accepted Answer · 2015-04-20T18:37:42

I got your script to work by simply adding a backslash to the FormulaPath string:

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

Excel VBA: Formula Not Entering Correctly From String

2 Answers