Need help with index, match in other workbook (formula via vba)

[bieke]

Hi,
I want to write a formula in a cell on a worksheet using <acronym title="visual basic for applications">vba</acronym>.
The formula holds an path name for a certain folder.
I can get the data out of the file in the folder, but it seems that excel doesn't find the file (File_Name), since I got a window showing the folder data and I have to click the right file.
How can I solve this?
FolderName is a string and Item_Name is an integer
File_Name is not a variable but the actual name of the file

This is the code I've used:
Worksheets("Test").Cells(1, 1).Formula = "=INDEX('" & FolderName & "\[File_Name " & Item_Name & ".xlsx]Sheet1'!$1:$1048576,MATCH(Sheet1!B5,'" & Folder & "\[File_Name " & Item_Name & ".xlsx]Sheet1'!$A:$A,0),11)"

Thanks for the responses!

 

[Andrew Poulsom]

Maybe the problem is your use of Folder instead of FolderName in the MATCH part of your formula.

 

[bieke]

No, that was just a typo , I replaced the variable name with another to post it at het forum
Hope, you find another solution.

Worksheets("Test").Cells(1, 1).Formula = "=INDEX('" & FolderName & "\[File_Name " & Item_Name & ".xlsx]Sheet1'!$1:$1048576,MATCH(Sheet1!B5,'" & FolderName & "\[File_Name " & Item_Name & ".xlsx]Sheet1'!$A:$A,0),11)"

Maybe the problem is your use of Folder instead of FolderName in the MATCH part of your formula.

 

[Andrew Poulsom]

What would the formula be if entered in a cell? And what do the variables FolderName and Item_Name contain?

 

[bieke]

What would the formula be if entered in a cell? And what do the variables FolderName and Item_Name contain?

In a cell the formula would be:
=INDEX('F:\4_personal_folder\Bieke\Planning files\[File_Name 2014.xlsx]planning'!$1:$1048576,MATCH(Planning!B5,'F:\4_personal_folder\Bieke\Planning files\[File_Name 2014.xlsx]planning'!$A:$A,0),11)

In vba:
Worksheets("Test").Cells(1, 1).Formula = "=INDEX('" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$1:$1048576,MATCH(Planning!B5,'" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$A:$A,0),11)"

FolderName is a string and would contain: F:\4_personal_folder\Bieke\Planning files
Item_Name is an integer and in this case it would contain 2014

If I write it manually in the cell, it does work, but if I write it in the cell using vba, then it failed

Has it something to do with the ' ?

 

[Andrew Poulsom]

With those values assigned to the variables the cell would contain exactly the formula you posted. Does this return the formula you would expect in the Immediate window?

Debug.Print "=INDEX('" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$1:$1048576,MATCH(Planning!B5,'" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$A:$A,0),11)"

 

[bieke]

With those values assigned to the variables the cell would contain exactly the formula you posted. Does this return the formula you would expect in the Immediate window?

Debug.Print "=INDEX('" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$1:$1048576,MATCH(Planning!B5,'" & FolderName & "\[File_Name " & Item_Name & ".xlsx]planning'!$A:$A,0),11)"

No, I also get the window named "update values File_Name 2014", but, I've found the problem :$
The file was not in the folder I thought is was :$
Sorry.
But thanks for the help!!!