问Excel VBA -具有可变文件名的VLookup

EN

'Populating the textbox
Private Sub openDialog1()

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
  .AllowMultiSelect = False
  .Title = "Please select the report."
  .Filters.Clear
  .Filters.Add "Excel 2003", "*.xls"
  .Filters.Add "All Files", "*.*"
  If .Show = True Then
    FilePath1 = .SelectedItems(1)'The full File path
    ary = Split(.SelectedItems(1), "\")'Splitting the file name from the file path
    TextBox1.Value = ary(UBound(ary))'Displaying just the file name and extension

  End If
End With
End Sub
'The second textbox is filled the same way.


'VLookup using a cell in File 1 vs. the column in File 2
Private Sub Vlookup()

Windows(TextBox2.Value).Activate
myFileName2 = ActiveWorkbook.Name
mySheetName2 = ActiveSheet.Name
myRangeName2 = Range("B2:B2000")

Windows(TextBox1.Value).Activate
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select

Range("F2").Formula = "=VLOOKUP(E2,[" & myFileName2 & "]" & mySheetName2 & "!" & myRangeName2 & ",1,0)" ' Having issues with the syntax here.

Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F2000")
End sub