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