Open workbook based on value found in range

[hutch@edge.net]

Good morning, Gurus.

I am trying to open a specific workbook, from several books on a shared drive, based on a value found in a range of cells.

For example, range A3:B3 will contain a number, and cmay also contain some text, but based on the number found in that range, I want to open or activate a particular workbook.

Let me know if further clarification is required.

Thanks in advance.

Jerry

[royUK]

What is actually in the cells? Attach an example.

[hutch@edge.net]

Morning, Roy.

The attached workbook contains 2 example of the types of responses I may find in row 3 of the worksheet titled "Quantity Changes". Some of the suppliers are putting the supplier number in cell B3 of the worksheet, but some are putting it in A3, along with the row header, "Vendor #:". Thats the only reason I need to search for the number. I figured if I could search for the number, it would save me having to make the suppliers change their programming again.

The workbook is attached and, as always, I appreciate your expertise.

Hutch

[royUK]

Is the workbook to open the same named with the number entered?

[hutch@edge.net]

I haven't built the workbooks yet, but I'm thinking they will be named "PO Response Tracking - 014619", "PO Response Tracking - 010299", etc.

[hutch@edge.net]

Good morning, All.

This is just a bump to get back to the top of the list.

Any suggestions appreciated.

Thanks.

[hutch@edge.net]

I'm guessing that if the mods on this site don't know how to do it, it can't be done.

Back to the drawing board, I guess. Thanks anyway.

[royUK]

You need to lock cells that you don't want suppliers to enter into & make sure the data entered is in the correct format. this will make processing much easier.

[hutch@edge.net]

I agree with your sentiments, Roy, but here's my problem. The forms I am receiving are being sent by over 100 suppliers. The smaller ones may be simply entering information into the template I sent, but most of the larger ones are dumping the text file sent by the buyer into their system, and IT has programmed their system to output it in our format. It has been such a hassle getting everyone on the same page up to this point, I thought it would be easier to allow for errors on my end than to get them all to maintain compliance on their end.

The numbers I am "searching" for and trying to use to open another workbook are the individual supplier numbers. Most of them have this number listed in cell B2, and "Vendor #:" in A2, but some of them have "Vendor #: 112345" listed in A2. Also, since some vendor numbers begin with "0", Excel usually leaves the "0" out of the number, because they didn't format the cell as text prior to entering the number. This is why I was wanting to search the contents of that range to look for the number, and then open the appropriate book based on the number found.

Alas, too many variables can ruin even a good code, so unless you know of a potential solution, I'll have to start working with the suppliers to try and get a standard output from each of them.

Thanks again for all your time.

Hutch

[royUK]

See if this selects the right number, let me know 7 then we can add code to open the workbook.

Please Login or Register  to view this content.

[hutch@edge.net]

S'up, Roy?

There was an error. I changed the "End if" at the end of the sub to an "End with", and it ran.

Please Login or Register  to view this content.

The contents of cells A2:B2 on the sheet I ran it on were:
A2 B2
Vendor #: 13730

The message bos that appeared said - "dor #:", so it trimmed out the first 3 letters of A2.

When the contents of cell A2 is changed to:
Vendor #: 13730

The message box showed 13730, so apparently it works in some cases.

Thanks for trying!

[hutch@edge.net]

S'up, Gurus?

I'm working with the suppliers to get them all in the smae format, which means I will be able to use the value of a CELL rather than a range to find which workbook to open.

Cell B2 will contain the vendor number. The workbook I want to open will be on a network drive, and will be titled:

Please Login or Register  to view this content.

What's the proper syntax to open that file?

Thanks.

[hutch@edge.net]

Nevermind. The syntax I was looking for was :

Please Login or Register  to view this content.

Have a good one!