Index and Match with VBA

[rex759]

My ultimate goal is to read a cell from a closed workbook.


The code below asks the user to select a workbook so I can obtain the location of the file (name doesn’t change) which is placed on a worksheet. Looks something like this,

A2=C:\Documents and Settings\joe\Desktop\Projects\Data.xlsx

I thought I could manipulate the information so I can use index and match but I can’t seem to incorporate it.

This formula to pull out the filename
A7=LEFT(A2,FIND(".",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-5)

This formula to add the filename with correct syntax
A14="'"&A7&"[data.xlsx]2011'!"

Index and Match
D10=INDEX(A14&$B$2:$B$2000,MATCH(B2,A14&$A$2:$A$2000,0),0)

Code to add file location

Sub IndividualWkbks()

Application.ScreenUpdating = False
Dim myFileName As Variant

myFileName = Application.GetOpenFilename("Excel Files, *.xlsx")
 If myFileName = False Then
Exit Sub 'user hit cancel
End If

MsgBox (myFileName)

Range("a2") = myFileName

Application.ScreenUpdating = True
End Sub

Anyone have a better way of doing this?

Thank you,

 

[AlphaFrog]

This might help...

Read information from a closed workbook using VBA in Microsoft Excel

If you do a web search for; Excel read values from a closed workbook, you'll find other examples as well.