Opening folder from selected cell

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
Code I have to open a folder in relation to range being always the same cell. So that specifict cell we call roll. I enter a roll number 123456. It automatically opens the path directory to that roll specifically. When done we save it. Then we can put in another number in that same cell and it will open this new roll folder. What I want to do is be able to have one button on top

then enter a number in cell A1 then another number in cell A2 and so on. From the click on cell A1 and then click the button and it opens that roll folder. After click on Cell A2 that has a different roll number and it would open up that folder.

I am guessing where it says in my code bellow JobNo = Range("Roll").value I would need "roll" to be the actual cell I clicked on before pressing that Button to run the macro. the number in those cell are always actual numbers

Really hope someone can help with this. Probably silly but tired of looking for it
thank you


Sub OpenProjectFolder()

Dim JobNo As Long
JobNo = Range("Roll").Value

IncomingPath = "J:\Paper\VPHW\Shipping\INCOMING\"



IncomingPath1 = IncomingPath & ((JobNo \ 10000) * 10000) & "\"



IncomingPath2 = IncomingPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value



If Dir(IncomingPath2, vbDirectory) <> "" Then

ret = Shell("explorer " & IncomingPath2, vbMaximizedFocus)

Else

ret = Shell("explorer " & IncomingPath, vbMaximizedFocus)

End If
End Sub
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Perhaps this might do what you are looking for
VBA Code:
Sub OpenProjectFolder()

    Dim JobNo   As Long
    Dim sPath   As String
    Dim sPath1  As String
    Dim sPath2  As String
    Dim Ret     As Long
    
    sPath = "J:\Paper\VPHW\Shipping\INCOMING\"
    
    With Selection
        If Not .Count > 1 Then
        
            JobNo = .Value
            sPath1 = sPath & ((JobNo \ 10000) * 10000) & "\"
            sPath2 = sPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value
            
            If Dir(sPath, vbDirectory) <> "" Then
                Ret = Shell("explorer " & sPath2, vbMaximizedFocus)
            Else
                Ret = Shell("explorer " & sPath, vbMaximizedFocus)
            End If
            
        End If
    End With
End Sub
 

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
so funny I just found it

Sub OpenProjectFolder()

Dim JobNo As Long
JobNo = ActiveCell.Value

IncomingPath = "J:\Paper\VPHW\Shipping\INCOMING\"



IncomingPath1 = IncomingPath & ((JobNo \ 10000) * 10000) & "\"



IncomingPath2 = IncomingPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value



If Dir(IncomingPath2, vbDirectory) <> "" Then

ret = Shell("explorer " & IncomingPath2, vbMaximizedFocus)

Else

ret = Shell("explorer " & IncomingPath, vbMaximizedFocus)

End If
End Sub
 

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
Perhaps this might do what you are looking for
VBA Code:
Sub OpenProjectFolder()

    Dim JobNo   As Long
    Dim sPath   As String
    Dim sPath1  As String
    Dim sPath2  As String
    Dim Ret     As Long
   
    sPath = "J:\Paper\VPHW\Shipping\INCOMING\"
   
    With Selection
        If Not .Count > 1 Then
       
            JobNo = .Value
            sPath1 = sPath & ((JobNo \ 10000) * 10000) & "\"
            sPath2 = sPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value
           
            If Dir(sPath, vbDirectory) <> "" Then
                Ret = Shell("explorer " & sPath2, vbMaximizedFocus)
            Else
                Ret = Shell("explorer " & sPath, vbMaximizedFocus)
            End If
           
        End If
    End With
End Sub
not sure it would have worked, it is very possible but found it just before... Thank you so much
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome. Glad you sorted it.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,198
Members
412,706
Latest member
msousa25
Top