VBA Macro button to open a folder derived from the (partial) name in a selected cell

[Jeroen89]

Hi all

I run Excel 2016 on Windows OS.
My goal is to create a macro button which would open the folder location, based on partial data (left,7) from my current selected/active cell.
This should allow me to select any cell which has viable data in it and open the direct folder on our server of it.

Example of the data in my current selected cell: AN0089V ISSEP SV
Example of the folder's path I require to retreive: H:\BEND\000. TELECOM SITES\TELENET\AN\AN0089V

I do run a similar method directly out of a hyperlink function: =HYPERLINK("H:\BEND\000. TELECOM SITES\TELENET"&""&LEFT(A10;2)&""&LEFT(A10;7)
This formula is however linked to fixed data (A10). In my current worksheet I don't have fixed data and this would like to apply that formula to my selected cell.

The VBA code I have so far, allows me to open up the destination folder (TELENET) but I can't manage to get the .left function in VBA code to work properly, in order to get to the final destination.
Additionaly, the part where I link that .LEFT function to my selected cell (twice), first for the first 2 letters, than for the 7 first letters, I also don't get to work.

This is what I manged to work:
Call Shell("explorer.exe" & " " & "H:\BEND\000. TELECOM SITES\TELENET""", vbNormalFocus) --> Opens the TELENET folder, the easy part. The next step is to open a folder based on cell date from my selected/active cell.

At this point I got stuck:
Call Shell("explorer.exe" & " " & "H:\BEND\000. TELECOM SITES\TELENET""" & "" & " left(ActiveCell,2)" & "" & " left(ActiveCell,7)", vbNormalFocus) --> somehow this prompts me to the 'documents' folder.

Feel free to provide some help, would be much appriciated :)
If additional info is required, please don't hesitate to ask.

[Kenneth Hobs]

Welcome to the forum! When building a string, I like to do it simply so I can verify that it was built right.

Sub Main()  
  Dim s$
  s = "explorer.exe " & """" & "H:\BEND\000. TELECOM SITES\TELENET\" _
    & Left(ActiveCell, 2) & "\" & Left(ActiveCell, 7) & """"
  Debug.Print s
  Shell s, vbNormalFocus
End Sub

[Paul_Hossler]

What's in the ActiveCell?

[Jeroen89]

Welcome to the forum! When building a string, I like to do it simply so I can verify that it was built right.

Sub Main()  
  Dim s$
  s = "explorer.exe " & """" & "H:\BEND\000. TELECOM SITES\TELENET\" _
    & Left(ActiveCell, 2) & "\" & Left(ActiveCell, 7) & """"
  Debug.Print s
  Shell s, vbNormalFocus
End Sub

Works excellent, just as intended. Thanks alot Kenneth!!! Much appriciated!!!
Belgian greetings.