Searching through a directory using a partial filename

[madadd]

Hello!

I am in need of help. I am trying to automate a very manual task in which I scour a bunch of excel files to copy and paste some info. So, our work orders (WO) are all created off a premade template. so the information I need is always in the same place on every file. I need a formula/program that can search a directory for a PARTIAL filename, and get info from a particular cell.

I say partial because the information I enter will be the beginning of the filename. For example: all our file names follow this format: (WO) (company) (State) (City).xls.

I want to type in SAL1234 (the WO) into Column A and have returned the value in $J$2 of the file 'SAL1234 RandomCorp TX Dallas.xls' in Column B for example.

I've spent this morning with NBVC trying to figure out a Formula funtion, but I get roadblocked by not having the full, accurate filename. And now I'm here

Oh and in case you're interested or need clarification, the other Thread I created is here: http://www.excelforum.com/excel-gene...cel-files.htmlgetting VBA help of which I've never used before.

[Leith Ross]

Hello madadd,

This macro will take the entry from any cell in column "A" and check if the first part of any workbook (.xls) file name in the specified directory matches the cell contents. If a file is found then the contents of $J$2 on the the first sheet are returned in column B. Change the file path before installing the macro. The path is marked in bold.

Worksheet Change Event Macro

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro

1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[madadd]

Thank you for the help, but I'm afraid I'm having some trouble. I've done as you instructed and changed the filepath. And when I go into the workbook and type in the beginning of one of the names, I can see the workbook flicker a little, the mouse goes to the hourglass for a split second, but ultimately nothing happens in column B.

I've tried creating a newworkbook, copying over the code to sheet 1, and playing around with different WO numbers. I've tried creating a test lookup document using our template and placing it in different folders, all the while appropriately changing the filepath.

As I write this, I've just tried creating a complete blank test lookup document, and that will work. So now at least I think I've identified the problem as being with our template document. I've tried pointing to different cells and nothing.

Any advice?

Oh and also, would it be possible to use a higher level filepath to be able to search multiple folders of .xls documents? For example instead of using the filepath
C:\Documents and Settings\tempe\My Documents\My Received Files
I would like to use:
C:\Documents and Settings\tempe\My Documents

I've tried this change to the code, but it doesn't currently work

[Leith Ross]

Hello madadd,

If you can post the workbook, it would help. Sub-directories can be searched recursively or to a particular depth. How deep to you want to go in MyDocuments?

[madadd]

Wow, that was quick. Thanks. And I only need to go one level up.

Lets see if this attachment worked...

[Leith Ross]

Hello madadd,

Since there are no macros in this workbook, I have a couple of questions.

1. Is the $J$2 cell you are referring to on the "Pick Ticket" worksheet?
2. When a new Work Order is saved, is it only the "Work Order" sheet?

[madadd]

Well, the J2 was an example, normally it'd be the PO#, so J6. And alot of the Pick Ticket sheet is referrenced to info in the Work Order sheet.

And you mentioned there being no macros, this is true. And remember, this is the type of sheet where I'm trying to pull the information, not my summary sheet (where the macro will be used).

[Leith Ross]

Hello madadd,

Here is the update macro. Let me know how it works out.

Please Login or Register  to view this content.

[snb]

I made a UDF (User defined Function)

Please Login or Register  to view this content.

In the cell where you want to see the result;

=snb("Path","extension",number) 

So if you want the 5th xls-file in "E:\OF\"

=snb("E:\OF\","xls",5) 

[nimrod]

Hello Leith & madadd

GET DATA WITHOUT OPENING WORKBOOK

I'm looking at your code and I've noticed that you are actually opening and then closing the workbook you want the information from. Are you aware that that information can be obtained without opening the workbook ? The use of command "ExecuteExcel4Macro" along with the path, filename , sheet and cell will return the value from the closed book.

Here's a little demo of what i'm talking about ...

Please Login or Register  to view this content.

[madadd]

The UDF idea is nice, but as I understand it (and correct me if Im wrong I'm not practiced in the excel programming ways) I would have to input the filepath and its position based on its order in the folder? This seems a bit complicated. Again, I am given a partial filename. The manual labor I do currently, involves me searching through our shared drive to find the file, open it up, and extract the PO# among other things. I can see your UDF working well IF the files were so sequentially numbered. Actually they are, but there are duplicates and triplicates of say a SAL1234 file. There are Change orders and Customer Copies which are saved as new files with slightly different endings (like a CC before the .xls) in the same directory. The reason I haven't seen this as an issue before is b/c they all contain the same information (mostly), at least the info that I need to grab.

Again, that is only my interpretation of what you have. So I apologize if I'm completely off base.

Leith Ross,
I tried inputing the updated code and it doesn't appear to do anything. As I had stated the first time around, there was a small 'hiccup' when I could clearly tell the program was running through the macro; with the new code, there is no sign of the macro running. Even on my previous test files I can't get anything.

If it helps, the actual file path I use is "U:\Work Orders\Work Orders 2011\SAL 3300-3399" and so thus in the Filepath of your new code I've tried "U:\Work Orders\Work Orders 2011". I do get errors when I point to the actual filepath, but you may have expected that.

With each new iteration, I've been slowly trying to at least comprehend which lines do what. As I may have hinted before, I ultimately will be grabbing 3-4 things off of those sheets I sent you, all lined up in a row. But so far I think I'll be able to alter the code to do that. Until then I am still in great need of your assistance with the core functions.

And thank you for your help so far

[Leith Ross]

Hello madadd,

I created a directory and saved several copies, with different names, of the program you posted. The new names were input onto a sheet in column "A" of the test workbook with the macro. The macro ran perfectly in my tests returning the value in J6 to the cell in column "B". Obviously, you are doing something differently but what that is I am not sure.

That being said, can you provide a detailed outline of the steps you are taking when running the program?

[snb]

About the UDF:
It has nothing to do with the names given to files.
It has got everything to do with the order in which the method dir encounters a file according to the criteria.

if you put in F1:

=snb("Path","Extension",Row()) 

and autofill that to F200 you will see what I mean.

[madadd]

Leith,
Here are the files I'm using, test2.xls is the summary sheet with your code and SAL3399 test CO longmont.xls is my test retrieval page. The retrieval page is located at 'U:\Work Orders\Work Orders 2011\SAL 3300-3399' hence why I put 'U:\Work Orders\Work Orders 2011' in the filepath.

Nimrod,
Does that function work with wrkbks that are opened? There are some situations where the file of interest may already be opened.

snb,
This got me a list of all the files in the folder. Are you suggesting that I use this in conjuntion with what NBVC came up with using formulae? (see link to that thread at top of the post).

[madadd]

Leith,
I went through the code placing Debug.print statements all over to see how many times a certain instance was hit, or even if it was. Surprisingly, the code never runs the last IF statement. Or rather it always comes up as false.

[Leith Ross]

Hello madadd,

Perhaps this has to do with using a mapped drive. I don't have a local network to test the code to be sure. I used your workbooks and created a folder with a subfolder that has the WO in it. When tested using the macro below, it works. The only difference between this macro and the one in the Test workbook is the message. This version will alert you if there are no subfolders in the directory.

You should check your network settings and permissions to be sure your are being blocked or denied access.

Please Login or Register  to view this content.

[madadd]

Thank you for responding. I tried your code in a new sheet, and it isn't working once again for me. As I said, I think there's a problem with the IF statement. However, after A LOT of trial and error, and using the Debug.print I have come up with a working solution...though I think it's a little messy.

Please Login or Register  to view this content.

I don't particularly like the use of left() but it was kind of a work around for the possibly faulted IF statement. And also, could you be so kind and tell me what this does:
Set SubFolder = oShell.Namespace(SubFolder)

It worries me a little only because when I do a 'Debug.print Item' right after:
For Each Item In SubFolder.Items
It will display maybe 3/4 of the files plus some that would be in other completely different folders. For example, it'll display SAL3332-SAL3399 which is all in the same folder but it doesn't display SAL3300-SAL3331 which is in the same folder. The weirdest part of which is that I can still retrieve my J6 cell from any of those non displayed WOs. So maybe there's not really anything wrong with it?

*I also had to use your old 'Target.Offset(0, 1).Value = Sheets(1).Range("$J$6").Value' The new one gave me errors when I opened up the IF loop.

**Oh and also, I lied about the Sheets(1).Range("$J$6"). For some reason it only works with Sheets(2) the Pick Ticket one and not on the first work order one...any ideas on that?

[Leith Ross]

Hello madadd,

The Like operator is a standard VBA language component. If you are having to use the Left function because the Like operator is failing then your VBA platform is corrupted. For now, if Left works then use it.

As for the statement :

Please Login or Register  to view this content.

This assigns the Folder object named by SubFolder's value to the Variant variable SubFolder. The Folder object is needed so the macro can examine what is in it.

[madadd]

Dang, I think you're right. I tried some simple comparisons with the Like function and nada. I don't suppose there's a simple fix for that? And you don't know why I can retrieve things from the second sheet, but not the first?

Thanks.