Need to get the name of the File in a Vlookup VBA using wildcard characters

[e4excel]

Need to get the name of the File in a Vlookup VBA using wildcard characters

Dear Forum,

I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...
Ex:
I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..

My lookup is very simple as of now and I do not know how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...


Regards
e4xcel

[Tinbendr]

Use GetOpenFilename to navigate to the file and select. Then use the variable in the VLookup. See example in help file.

[e4excel]

Thanks but where if the Help File and moreover If I want to avoid the FileOpen Dialog box and jst streamline the filename based on the Uniqueness of the name "Mapping" instead then it would be more easier..

[Tinbendr]

where if the Help File

In the VBE, Type GetOpenFilename, then press F1.

You'll have to acquire the filename first, then use it in the vlookup.

It would be easy enough to use the Dir function to get the filename.

Please Login or Register  to view this content.

This assumes that there will never be more than one match in the folder.

[e4excel]

Dear Tinbendr,

Thanks for the help and the code, however when i tried using it, just dint work, so please advise me what is wrong with my modification ?

Please Login or Register  to view this content.

Also what is the variable type for FN$ and why is it written with a "$" sign next to it ?

Please explain as I am not good in VBA..

Thanks in advance
e4excel

[Norie]

You need to open the file for Workbooks to work, otherwise you'll get a subscript out of range error.

[Tinbendr]

just dint work,

Your response it too vague to offer any solutions.

Please post two workbooks to illustrate your situation.

FN$ stands for filename string. The dollar sign is a shortcut in variable declarations.

It's tha same as; Dim FN as String.

Others include:
! - Single
% - Integer
# - Double
& - Long
$ - String
@ - Currency.

[e4excel]

Dear Tinbendr,

Thanks a lot for the explanation, I was totally unaware of this short-cut..

Please find both the files for reference, actually i am not getting any error but neither the answer I am looking for so I am sure I am missing out something..

Regards
e4excel

[Tinbendr]

Please Login or Register  to view this content.

[e4excel]

Thanks a lot Tinbendr, this works however I need to use it for an entire column then how do i use it in this style.

Please Login or Register  to view this content.

I liked the approach of finding the File with the path and then close..

Regards
e4excel

[Tinbendr]

Oh!, I didn't realize you just wanted formula on WB1.

Please Login or Register  to view this content.

[e4excel]

Dear Tinbendr,

Thanks again , however I would really appreciate if you could tweak just a little more to get the code in this style as for the explanation I had used Rows A2:A4, however in reality I would be having dynamic rows and I therefore would use 2 Variables to include/mark the lastrow range.

Please Login or Register  to view this content.

But when i try using this , it does not work....and gives the error Object required...

Now is it because we cannot have Set properties for different workbooks..

Please Login or Register  to view this content.

This works but I am not sure how so can you please help me improve on this code ...
Thanks again....








Regards
e4excel

[Tinbendr]

OK, time for some clarification!

WHAT IS THE RESULT YOU WANT?

Describe what you're trying to accomplish.

If all you're doing is pulling ages from WB2, based on a value in WB1, then we need to use Find and not vlookup.

[e4excel]

NO i managed somehow to get what I wanted and thanks to you..

Thanks once again....

Just one small query if i had to find a sheet in the same file using wildcard, then what would be the code..

Regards
e4excel