Sub insertCode()
 
    Dim fullPathToStock As Variant
    Dim stock As Excel.Workbook
    Dim pathToStock As String
    Dim stockFile As String
    Dim sheetStock As String
    Dim formula1 As String
 
    Dim bom As Excel.Workbook
    Dim pathToBom As Variant
    Dim bomFile As String
    Set bom = Workbooks(ThisWorkbook.Name)
 
    pathToBom = bom.Path
    bomFile = bom.Name
 
    fullPathToStock = Application.GetOpenFilename("Excel Sheets(*.xls), *.xls", _
      1, "Open stocklist", "")
    Set stock = Workbooks.Open(fullPathToStock)
    sheetStock = stock.Worksheets(1).Name
    stockFile = stock.Name
    pathToStock = stock.Path
    formula1 = "SVERWEIS(B3;'" & pathToStock & "\[" & stockFile & "]" & _
      sheetStock & "'!$A$15:$B$178;2;0)"
 
    With bom.Worksheets(1)
        .Range("A3").Value = formula1
        .Range("I3").Value = "SVERWEIS(A3;'" & pathToStock & "\[" & stockFile & _
          "]" & sheetStock & "'!$B$15:$G$178;6;FALSCH)"
    End With
End Sub