How to use part of filename without changing the filename

[GreggR57]

I have a packing list, where the user selects a product from a drop-down list. The filenames are usually like "xxx-xxx.xlsm". My problem comes when they have a product with a filename of "XX-10XX XX-X 4up 2High.xlsm", sometimes it's longer than that. The packing list can only use the actual product number,(XX-10XX XX-X), but needs to access one of the sheets in that file, but because of the rest of the name, the macro doesn't "see" the file, and won't get the needed info. Unfortunately I can't change the filename as there are variations in each file that need to be separated.
How it works...

The user selects the product, xxx-xxx, on the packing list, the macro then looks at the file, xxx-xxx.xlsm/"Retain Sheet" cells "C1 and E1" for the line number and Team letter respectively, and fills out the pallet ID#. A PO# can be used if available and needed. The idea is to automate, and take as much human error out as possible. When the packing list is printed, the last three numbers in the pallet ID advance by 1.
Can anyone help, please?

Thank you in advance to anyone who can help. If more info is needed, please let me know.

[arnelgp]

see if this modification (of code) will solve your problem.

[GreggR57]

Hi arnelgp,
Thank you for the reply. My apologies for the code the way it is. The section of code you worked on is only relevant when a PO# is wanted/needed. The relevant section of code is in new pic. For some reason I can't post the code.

The highlighted code is what happens when/if a product is selected in cell H1 on the sheet Barcode Packing List. The file should already be open, if not, the packing list just needs to be reselected/activated and cell K7 will fill in with the pallet ID# after a product is selected. In the picture, one of those products should already be selected and showing in cell H1, and the file open, only then would K7 be filled out. Otherwise it would say Please fill out the Retain Sheet.

Thank you again.

Gregg

[arnelgp]

ok, i also copy the same code to the Activate event.