Can I open a file by searching for first six characters?

[modytrane]

I have a number of project files that are maintained on the network.
These files are named "XXXXXX Project Name.XLS".
XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.
We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.

I have created an excel program that extracts project numbers and related financial data from the report [from home office].


I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.

The problem is opening the file based on project number only, while the file names contain Project Name as well.

So, can I look up a file by only searching for first six characters of the name?

Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.

Any help or suggestions appreciated.

modytrane.

[shg]

See http://www.excelforum.com/excel-prog...-the-name.html

[modytrane]

I am ataching a sample file.
There is a list of project numbers in column A.
I have posted the pathname and filename for the first project number in Row 2. The problem is that the project name which is part of the filename is not known to my program. I receive a list of numbers in the financial records from home office. Then I have to find the respective file on our local network. Each project number only has one file, so if I can find the first matching file, its ok.

I have read about solutions using DIR function, but I can't get it to work.

What I really need is a loop to go through all numbers in column A and open the respective file, run a macro inside the file, save it and go on to next one.

Any help or suggestions appreciated.

I looked at the thread suggested by Shg, but can't make it happen.

Thanks,
modytrane

[Leith Ross]

Hello modytrane,

This macro should help. This will locate any Excel workbook with the project number in "A2" that occurs in as the first part of the file name.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[shg]

I think you need a little more code inside the loop, Leith.

[Leith Ross]

Hello Modytrane,,

SHG, thanks for spotting that! A line of code is missing. Should be this...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[modytrane]

Leith and Shg,
Thanks for your responses.
In my case, the wildcard needs to be in the directory / pathname.

My actual filepath / filename would be:

M:\Projects\2008 Projects\B608049 Office Bldg\Product Cost Estimate V1.1.xls

The wildcard would be Office Bldg.
The filename is always the same.

I am sure its a small adjustment to the code, but I couldn't figure it out.

Thanks,

[Leith Ross]

Hello modytrane,

This macro will find all folders beginning with "B608049" in the directory "M:\Projects\2008 Projects\" and attempt to open the "Product Cost Estimate V1.1.xls". It assumes there is only one file with this name per folder. This request concerns a folder name and not a file name, along with several of differences. Please review your posts for accuracy and completeness before you post.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[modytrane]

Leith,
Thanks a lot for your response and sorry for the misleading title for this thread.

Your code did not work right away, I had to modify a little as follows.

Please Login or Register  to view this content.

The change was made in the GetAttr function.

So now that part is working. It goes throug the directory and searhces for folder "B608004*" and looks for file "Product Cost Estimate V1.1.xls".

Now I need help with one item. The partial folder names e.g. "B608004" are listed in A2:A50.

!. I would like to make this code go through this range [A2:A50] and locate the folders and open the .xls files one at a time. Every time it opens a file, I would like to execute a macro in that file, save the file, close the file and then go to the next one. I already have the macros set up in each file.

2. Go though the range A2:A50 until it comes across an empy cell. In other words, the range may not be full. Number of projects vary month to month.

You have been of great help and source of learning. In order to make your code work, I read through the description of DIR and GetAttr functions. I felt pretty good, when I got it to work.

Keep up the good work,
Thanks,
modytrane.

[modytrane]

Leith,
I appologize, the working code is as follows:

Please Login or Register  to view this content.

Two changes were made, in GetAttr and Workbooks.Open functions.

modytrane.