Excel VBA Variable File Name in Lookup Formula

[amcintosh]

Hi

I'm trying to use VBA to insert a formula into in excel spreadsheet that contains file path

Snippit of the codes

Dim sdpath As String, sdfile As String
sdpath = "C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\"
sdfile = "Sales Report Data.xlsx"

Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'[" & sdpath & sdfile & "]Data'!C1:C2,2,FALSE)

Returns this in the Excel Spreadsheet:
=VLOOKUP(D64,'[C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\[Sales Report Data.xlsx]Data]Sales Report Data.xlsx]Data'!$A:$B,2,FALSE)

anybody got an idea so it'll look like this:
=VLOOKUP(D64,'[C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\[Sales Report Data.xlsx]Data'!$A:$B,2,FALSE)

Cheers

PS This is the full code - however this is still a work in progress

Dim w As Workbook, w2 As WorkbookDim ed As Worksheet
Dim sdpath As String, sdfile As String


Sub opendatafile()


'Choose Sales Report Path here.
'development - Andrew's home PC
sdpath = "C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\"
On Error GoTo open1
sdfile = "Sales Report Data.xlsx"
Set w = Application.ActiveWorkbook
Set w2 = Workbooks(sdfile)
Set ed = w2.Worksheets("Data")




    w2.Activate 'activates the sales report data
    w.Activate 'activates this workbook
Exit Sub


open1:
Workbooks.Open (sdpath & sdfile)
w.Activate


End Sub




Sub import()
Application.ScreenUpdating = False
Application.CutCopyMode = False






Call opendatafile




Range(Columns(2), Columns(3)).Insert shift:=xlRight
Cells(1, 2).Value = "Department"
Cells(1, 3).Value = "Customer"
Columns(12).Delete
Cells(1, 12).Value = "Buy Price"


Dim lr As Long


'data clean up


lr = 2000


For lr = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Dim DDD As String
    'Insert Department
        Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'[" & sdpath & sdfile & "]Data'!C1:C2,2,FALSE)"


        
'DDD = "'C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\Sales Report Data.xlsx'"
        'Range("B" & lr).Formula = "=vlookup(D" & lr & ",'[" & DDD & "]Data'!$A:$B,2,false)"


    'Insert Customers
        Range("C" & lr).FormulaR1C1 = "=VLOOKUP(RC[3],'[Sales Report Data.xlsx]Data'!R1C19:R5C20,2,FALSE)"
                
    'Delete Zero Quantity Line Lines
    If Cells(lr, 10).Value = 0 Then Rows(lr).EntireRow.Interior.ColorIndex = 7
    
    'Mark Credits
    If Cells(lr, 10).Value < 0 Then
    With Cells(lr, 2)
        .Value = "CREDIT"
        .Interior.ColorIndex = 45
        .Font.Bold = True
        End With
    End If
    
    'Delete Crates
    If Left(Cells(lr, 4).Value, 3) = "ZZ5" Then Rows(lr).EntireRow.Interior.ColorIndex = 35
    
    'Delete N/A Customer Lines
    If WorksheetFunction.IsNA(Cells(lr, 3)) = True Then Rows(lr).EntireRow.Interior.ColorIndex = 39
      
    'Delete Blank and GL Cells
    If IsNumeric(Cells(lr, 4)) = True Or IsEmpty(Cells(lr, 4)) = True Then Rows(lr).EntireRow.Interior.ColorIndex = 19






Next lr








Application.ScreenUpdating = True


End Sub

 

[Domenic]

Try...

Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'" & sdpath & "[" & sdfile & "]Data'!$A:$B,2,FALSE)"

Hope this helps!

 

[amcintosh]

Cheers for that.