Pull the Vendor Name from the Filename to Name the Worksheet

[[email protected]]

Good day again, Gurus. This one will be a challenge, if it is even possible.

Thanks to Sean in another thread I am now able to open all workbooks in a particular folder that match the month entered in an input box via the user, and can copy a worksheet from that workbook to a new workbook, consolidating the data from about a hundred workbooks into a single workbook: http://www.excelforum.com/excel-prog...ser-input.html

I have now run into a new problem. I thought that each workbook opened would have a field on the worksheet containing the Vendor ID # which I would reference to name the worksheet with the Vendor ID # when copying the sheet to the new workbook. Unfortunately, the Vendor ID # is not in the data, so the only way to get the vendor name or ID # when copying worksheets to a new folder is via the filename.

My goal is to get the Vendor Name from the file name, vlookup the vendor name against a list I will create which crosses the vendor name to the Vendor ID #, and then name the worksheet with the Vendor ID #. The only advantage is that all of the filenames are in a similar format:
Monthly PV Status and Usg - Vendor Name (04-08-2014).xlsx

Other than that, the Vendor Name could be one of about 100 different vendor names, of varying number of characters. I was hoping there was a way to pull out the data between the "-" and the "(", remove the space that would be left at the beginning and end of the Vendor Name, (if we pull everything between the characters mentioned above), and then vlookup against my list to get the vendor number.

Can this even be done, or are there too many variables coming into play? I'm open to suggestions from anyone who may know a better way to do it.

Thanks in advance for any help you can offer.

[[email protected]]

Bump, please. Thanks.

[[email protected]]

Is this not possible? Anybody?

[jaslake]

Hi Hutch

Here's one way though I don't like it

Please Login or Register  to view this content.

[[email protected]]

Thanks.for the reply, Jas. It looks like the code you offered will only work if the filename is "Monthly PV Status and Usg - Vendor Name (04-08-2014).xlsx". My issue is that the "Vendor Name" portion of the name varies, and can be any 1 of a hundred different actual vendor names. I tried somewhow applying the "*" wildcard to make it give the vendor name of each file that meets my search criteria, but could never get it to work. Here is the portion of the code that I can use to open each filename meeting my input criteria, the problem is getting a vendor number from the vendor name of the file once opened.

Please Login or Register  to view this content.

[jaslake]

Hi Hutch

That was just an example...replace this line of Code.

Please Login or Register  to view this content.

[jindon]

Try something like

Please Login or Register  to view this content.

Then Add

Please Login or Register  to view this content.

[[email protected]]

I think I can use this, Jendon. Using the Function seems to be the best way to get where I am going, (at least in my initial testing). As written below an input box ask the user to enter a string, and then select a folder to look in. it then finds all instances of files in that folder that contain that string, gives a message box of the vendor name, opens the file, gives a message box of the filename and then enters the filename into the next workbook. I think I can figure somehting out from here, and if not I will be back in a different thread. I'm marking this one as CLOSED. Thanks, guys.

Please Login or Register  to view this content.