Hyperlink to a Variable File Name

[roadwolf]

I have many project reports that I fill out each month. All are based off of a “master_shell_spreadsheet”. I pull up a particular project # (within the “master_shell_spreadsheet”) and certain data is automatically populated. At this point I need the “master_shell_spreadsheet” to be able to hyperlink and open to a file folder on my hard drive where I have a series of monthly updated files listed. I can get the file folder to open up the folder to give me a choice but it will not automatically choose and open the file itself.
Ex. say cell B6 = C202123. This is the xls file name and its value will change based off of the original input data as I described earlier.
So the actual file location for this specific file would be say: C:\Documents\Reports\Subcontractors\C202123.xls. However, the C20XXXX.xls will vary for each different report and I need it to go to the same folder location find the correctly referenced C20XXXX.xls report and open it. there are as many as 50 of these C20XXXX.xls files in the referenced folder. I could write 75 different hyperlinks and macros but this seems a waste of time because I know there’s a better answer out there.
I am relatively new at this so please dumb down your answer so I can follow.
Thank you in advance.

[stojko89]

This code is made for when you click a button. Make a button on the sheet and click it. It should take you to the file.
Or if you're making the hyperlink somewhere else then you need to tell me where the hyperlink will be set. or upload an example workbook.
Code:

Please Login or Register  to view this content.

[roadwolf]

I'm getting a run time error. When I debug it leads me to the line:

Application.Workbooks.Open strLoc & strName & ".xls"

If I mouse over the “strLoc” in this line it gives me the correct path but when I mouse over the “strName” it highlights “strName = empty”.
I did check the:
strNam = ActiveSheet.Range("B6").Value”
and when I mouse over the “strName” portion it shows the correct value that is in B6.

[roadwolf]

I missed the "e" in the "strName". I took it off & it worked GREAT.
Thanks for the help

Oh yea, and I cannot figure out how to set this post as "solved", although it is.
Thanks again

[stojko89]

Yeh that was my mistake

If you want to mark the thread as [SOLVED] then in the first post that you posted in here look at the right upper corner of that post where it says Thread Tools. Click that and select mark as solved

And don't forget to hit the * under the post that helped you