Conditional hyperlink based on cell value?

[Teejicus]

I'm working on a document that has a front page where you type in a site number, and this then uses various vlookups to the background data sheet of the same workbook to return values related to the site - i.e. address, post code, trading hours etc.

On our shared network drives we also have a folder for each site that we look after, and I would very much like to link these folders to my spreadsheet - so you could type in a site number, bring up the site details, and if you wanted you could then click a button / cell that would take you directly to that site's file on the network.

I currently just have a static link to the branch folders parent folder but I was wondering if it's possible to maybe use the site number in a vlookup that would return the correct folder address so that you could click on it and go directly there?

I've attached an example of what I'm trying to do - any ideas?

Thanks!
TJ

 

[chirayu]

Add a Vlookup in cell B5 for the PATH
Then add this macro into your file
Then either run manually or link it to a button

Sub OpenMyPath()

Dim Foldername As String
Foldername = Range("B5").Value

Shell "explorer.exe" & Foldername, vbNormalFocus

End Sub

 

[Teejicus]

chirayu you are a genius - thank you!

 

[Teejicus]

I seem to have hit another brick wall. The solution above worked brilliantly on Friday and I got to work inputting the file paths into the data tab so that the macro could pull them when the "Open my Folder" button was clicked.

But for some reason this morning it just opens My Docs. When I examine the macro and step into it, it shows "file not found" and highlights the last line of the script (Shell "explorer.exe" & Foldername, vbNormalFocus) I've checked and the folders still exist and there's been no change in server architecture or naming structure over the weekend. I'm a bit (a lot) of a noob at VBA and I'm having to dig deep into my C++ days for debugging, but I can't see anything that's wrong?

Initially I thought this may be an error with the path - I hadn't put in the full server path "comapny\server09\team1\site list\site folder", I had been using the insert hyperlink function which shortened it to "\team1\site list\site folder" - but when I typed in the whole file path it still does the same thing.

It is Monday morning and I'm only on my second cup of coffee so it's likely I'm losing my mind - any ideas anyone?

Cheers
TJ

 

[chirayu]

Try changing Shell "explorer" line to this

 Shell "C:\Windows\explorer.exe " & Foldername, vbNormalFocus

 

[Teejicus]

Unfortunately I'm still getting the same error. I've also checked that my explorer.exe hasnt moved and it's right where it should be. If I click the link on the data tab it works fine, I just can't seem to get the macro bit working right.

Time for a can of Relentless lol!

 

[Teejicus]

Sorted!

It looks like my initial suspicions were correct, something went wrong when I inserted the hyperlink and it got shortened. I've lengthened them to include the drive (G:\Sites) and now it's working fine.

Thanks for your patience and assistance!