0

I have a file with a list of PC hostnames I want to be able to connect to the C drive of a specific one by clicking in a cell or button or something.

Let's say I currently have the hostname in Column A. I use CONCATENATE to turn it into a proper network path \\hostname\C$ and put that in Column B.

Now how can I make it so I can just click on the cell in column B to open that location in explorer.exe? I have 450 PCs so i need to be able to specify the range, feed the network path into VBA and then open that in explorer.exe does this make any sense? :P

Would really, really appreciate any help. Thanks.

Alex
  • 3
  • 1

1 Answers1

1

Wrap the concatenated value in a "Hyperlink()". From then on it is clickable and it will open the explorer.

=HYPERLINK(CONCATENATE("\\";A1;"\C$");A1)

Or you put this code in your Worksheet code pane and double click the cells, where your links stand. But then you mustnt use it in combination with HYPERLINK.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 2 Then Exit Sub
Dim sh As Object
Set sh = CreateObject("Wscript.Shell")
sh.Run ("explorer " & Target.Value)
Cancel = True
End Sub
Julian Kuchlbauer
  • 895
  • 1
  • 8
  • 17
  • thanks, I did this and it just says "cannot open the specified file". I know the link is OK because I can paste it in Start > Run and it will connect fine. Any idea why this may be? – Alex Jul 19 '16 at 13:12
  • so how does your link look like, without the friendlyname property? – Julian Kuchlbauer Jul 19 '16 at 13:20
  • OK I figured out that it wasn't working because I usually need to enter an admin password when connecting to a network PC (for the current Windows session, I guess). Once I connect via Start > Run and enter the username/password, I can then connect to it from the Excel file, but not before I do that. Is there a way to work around this or is there just no way of doing what I want? I tried running Excel as admin but not working (I actually need to Run as Another user and enter my domain admin password) – Alex Jul 19 '16 at 13:36
  • i found this, maybe I can try working this into my spreadsheet. I think i'll definitely need to use some VBA for what I want, instead of just formulas (if it's even possible, that is) http://stackoverflow.com/questions/27248221/access-shared-network-folder – Alex Jul 19 '16 at 13:45
  • I'm probably doing something wrong, but your second piece of code doesn't seem to do anything. When I double click in a cell, it just tries to edit the formula, and if I disable the in-cell editing, nothing happens. Maybe I missed something? Thank you for your help so far. – Alex Jul 20 '16 at 06:28
  • Are the cells you want to "open" in column B? Please try the now changed code – Julian Kuchlbauer Jul 20 '16 at 06:30
  • Awesome, your last updated code works! Thank you so much for your help. – Alex Jul 21 '16 at 07:28
  • No problem, i am glad to be of service. – Julian Kuchlbauer Jul 21 '16 at 07:30