Using a wildcard in path (not filename) with Dir function

Hello,

I need to access a directory which consists of a name and a code. I have a file in which the codes always match with the code in the directory-name, but not the names in the directory-name, hence I cannot use the name to access the directory.
The directories look like this:\\Map\map\Name - code\map
For instance: C:\map1\map2\map3\Grontmij - NL0010200358\2013
In that directory I just need to open the excel file which is there, but I can't find a way to get there unless I have the exact directory, which keeps changing.

Please Login or Register  to view this content.

This does not work, it seems to me the wildcard is not accepted in the path of the Dir() function, I have searched this forum, several others, used Google, checked the VBA reference sheet, but couldn't find the answer.

For those interested:
I have a file in which people have a reported a number of shares. In another file I have the correct number of shares. Stocks have a unique code, the ISIN. So, I have to check in file number 1 which ISIN it's about, then go to the correct directory in which file with the correct number of shares resides, open the excel file there, do the check and report if the number of shares is correct or not. These steps I can probably figure out myself, but accessing the correct directory is driving me nuts. Hopefully someone can help me!

Thanks in advance!

Maarten

ps, I can't figure out how you guys get those nice coloring in the code

Hello Maarten,

Welcome to the Forum!

Some more information about the files and their locations would be helpful.

Do the ISIN folders have a parent folder? For example ,"C:\2013\ISIN Folders" where each customer would be "C:\2013\ISIN Folders\customer name - NL0010200358"

If the answer to the question above is no then how do you determine the ISIN folder's path?

Hi Leith,

Thanks for the welcome, although I have been lurking for a couple weeks now, ever since I started building macro's

The ISIN folders indeed do have a parent folder, this is fixed though, but I will give a little more information since you asked

Every year at a specific date an inventory is made per stock how many customers have how many shares. If done for the stock Google the Excel would look like:
Stockname - ISIN code specific to the stockname - accountnumber of customer - customer name - number of stocks owned on the specified date

This will be saved in the following directory:
C:\accounting\stocks\Google - US38259P5089\2013\25-04-2013 positions.xls
(forgive the european dating system please )
There would also be a 2012 folder and 2011 folder etc, and next year there will be a 2014 folder.
I will always be needing the current year, so I just get the current year using

Please Login or Register  to view this content.

During a certain period of the year customers' positions (owned number of shares) have to be checked. The customers I need to check are delivered via another Excel sheet. The stockname on this sheet can differ from the name used in the foldername, for instance it might say 'Google A' (where the foldername is just 'Google'), but the ISINcode will always be the same. That is why I want to check for the ISIN in the name of the folder.

To retrieve all folders in C:\accounting\stocks\ .... US38259P5089, use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thank you snb! I must say though, the first suggestion does nothing (it returns an empty messagebox) but the 2nd works!

The problem now is that when I try to do a ChDir instead of a MsgBox command, the whole thing fails (error 76). I find this weird. Why would the messagebox return the correct information, but when I try to change my directory to that same information it breaks down?


Hmm... right after posting I suddenly had a brainstorm: The MsgBox returns the foldername (IE: Google - US38259P5089), not the whole foldertree-name. Might it be that the ChDir command tries to set the directory to that foldername instead of the whole foldertree?