lundi 20 avril 2015

Excel VBA: Formula Not Entering Correctly From String

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."


            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"


        End If

        Cancel = True

        Application.DisplayAlerts = True

        Application.ScreenUpdating = True

    End If

End Sub

1 commentaire:

  1. If you're looking for a reputable contextual ad network, I recommend that you try Propeller Ads.