Using Vlookup across two workbooks.

[ATAG_Wolf]

I have a macro in Workbook 1. the workbook is called "Detail Load Template FY15 VCA 3270100 revised.xls" that looks at a worksheet and exports the data to a txt file.

On the txt file I need to include some headings. One of the headings is a company code that needs to be found from a table back on workbook 1

The below code works and the vlookup places the cocode in the cell. But I want the workbook reference below can change when the macro is put in different named workbooks. So I need the Workbook name reference to be dynamic depending on the name of the xls spreadsheet it is operating in.

Dim strCoCode As Variant


strCoCode = "=VLOOKUP(b5,'[Detail Load Template FY15 VCA 3270100 revised.xls]ProfitCentres'!A:D,3,0)"    'look up strProfitCenter worksheet for CoCode and offset 3

Cells(1, 4).Value = strCoCode    ' Places the CoCode in cell D1.

I was trying the below that gives me the Workbook name correctly but not sure how I can work this into the Vlookup sum.

Function getbook() As String
getbook = ActiveWorkbook.Name

MsgBox "BookName is : $ " & getbook
End Function

strCoCode = "=VLOOKUP(b5,'GetBook & ProfitCentres'!A:D,3,0)"

naturally below does not work. So how do I get the result of Getbook into the vlookup ? can I have the Getbook insert its result to a cell and then have vlookup reference that cell?

 

[CharlesChuckieCharles]

You accidentally made getbook a string

try


strCoCode = "=VLOOKUP(b5," & "'[" & GetBook & "]" & "ProfitCentres'!A:D,3,0)"

 

[ATAG_Wolf]

You accidentally made getbook a string

try


strCoCode = "=VLOOKUP(b5," & "'[" & GetBook & "]" & "ProfitCentres'!A:D,3,0)"

Thanks, it works but not correctly. It returns the New Wrokbook book1 in the vlookup. This is what is happening and what I need.

Workbook 1 has lots of random sheet names and an 'Input' sheet. On the Inputsheet i have the name of that workbook getting pasted in cell i1 as the file name changes each month.
Finlename example in i5 is GUO Detail Load Template 2015 Budget with admits 05052014.xlsm

Then I copy all the data from the random worksheets to a new Workbook (no specified name) but in a header field in cell B4 on Sheet 1 and later on Sheet 2 I need to paste in a Vlookup sum that incorporates the original workbook file name. Currently you above code adopts the new book 1.
The Vlookup I have is

=VLOOKUP(b5,'[Detail Load Template FY15 VCA 3270100 revised.xls]ProfitCentres'!A:D,3,0)

The above workbook reference of course changes depending on the name of the file. Si it is that "[Detail Load Template FY15 VCA 3270100 revised.xls]" that changes each time. So I was using the below to try and capture the new name each time. Unfortunately I am calling strCoCoed in the new Book1 so it returns Book1.

    Dim strCoCode As String
    Dim strbookname As String
    
    strbookname = Range("A7").Value

Cells(1, 9).Value = getbook   'while in WB1  this gives me the correct name. puts it in cell A7


'while in new Book1
              ' placed here will get the workbook name of the temp workbooks created.
strCoCode = "=VLOOKUP(b5," & "'[" & getbook & "]" & "ProfitCentres'!A:D,3,0)"   this returns Book1.xlsx not the original workbook name.
Cells(3, 2).Value = strCoCode