VBA code to open Folder

[pateltarunt]

Hi
My requirement is to open project folder located in my computer D drive.
Condition is :-
I enter Partial Project Number/Name in Excel sheet Cell - Hit the button (contain vba macro) & my Project folder open that is available in D drive.

Example :-
In excel file if I want to enter only :- 123456
My Project Folder name is :- ABC-123456 which is located at :- D:\Users\tarun.patel\Documents\Tarun Patel\Project\ABC-123456

Please give me VBA code to full fill my above requirement.
Tarun Patel

 

[herofox]

hello ...must moving this Post To VBA Dep.
and you can to Use this Code

Sub openFolder()
Shell "Explorer.exe D:\Users\tarun.patel\Documents\Tarun Patel\Project\ABC-123456", vbNormalFocus
Dim strFolder As String
strFolder = "D:\Users\tarun.patel\Documents\Tarun Patel\Project\ABC-123456"
ActiveWorkbook.FollowHyperlink Address:=strFolder, NewWindow:=True
End Sub

 

[pateltarunt]

Thanks for reply.
But my requirement is that when I enter project number (not in full i.e. partial) in cell and hit the button it will search that particular project folder in main folder and from their open required number folder.

In short I have one main Project folder in my D drive and this main folder contain different project folder name given like "1234-ABCD" , "12345-ABCDE" , "123456-ABCDEFG" etc.
And in main excel sheet I will enter partial name i.e. 1234 / 12345 / 123456 etc and it will browse corresponding folder from my main project folder and open it.

 

[Keetoowah]

Hi, try this (Adapt the sheet name and the range where you enter project number):

Private Sub CommandButton1_Click()

    Const MainFolder As String = "D:\Users\tarun.patel\Documents\Tarun Patel\Project\"
        
    Dim Foldername As String, MyFolder As String
    MyFolder = ThisWorkbook.Sheets("Sheet1").Range("A1") '=======>> Adapt sheet name and range
    
    Foldername = Dir(MainFolder & MyFolder & "*", vbDirectory)
    
    Shell "C:\WINDOWS\explorer.exe """ & MainFolder & Foldername & """", vbNormalFocus
    
End Sub

 

[pateltarunt]

Hi Keetoowah
Thanks for solution. Code Works fine.
I have one more condition and is as under.

I have define one const path to open specific folder as per cell range.

I have two different client ABC and XYZ. and i don't know which project name is in which client. So I will define two Const path for my two different client i.e.
One Const with path "D:\Users\tarun.patel\Documents\Tarun Patel\Project\ABC\" and second
Const with path "D:\Users\tarun.patel\Documents\Tarun Patel\Project\XYZ\"

Now I will enter my project name in cell and it will first search using first Const path if folder find then open or else search from second path and open folder from there.

Please modified your code and send me. It will be a great help to me.

Regards

 

[Keetoowah]

Hi pateltarunt :)

thanks for the feedback, I hope I understand your request correctly. To be tested:

Private Sub CommandButton1_Click()

    Const MainFolder As String = "D:\Users\tarun.patel\Documents\Tarun Patel\Project\"
    Const FstClient As String = "ABC\"
    Const SndClient As String = "XYZ\"

    Dim Foldername As String, MyFolder As String
    MyFolder = ActiveSheet.Range("A1")            '=======>> Adapt range

    Foldername = Dir(MainFolder & FstClient & MyFolder & "*", vbDirectory)
    
    If Foldername <> VBA.Constants.vbNullString Then
        Shell "C:\WINDOWS\explorer.exe """ & MainFolder & FstClient & Foldername & """", vbNormalFocus
        
    Else: Foldername = Dir(MainFolder & SndClient & MyFolder & "*", vbDirectory)
        Shell "C:\WINDOWS\explorer.exe """ & MainFolder & SndClient & Foldername & """", vbNormalFocus
        
    End If
End Sub

 

[pateltarunt]

Hi Keetoowah
Thanks for reply with code. Its work fine. Thanks again

My one more question is on Protection and allow permission on protected sheet.

I have protect the sheet (with password = "1234") with allowing Select Unlocked Cell, Sort, Use Autofilter, Format Cells by checkbox tick mark. (using Excel Review - Protect sheet selection)

I have set
ActiveSheet.Unprotect Password:="1234" at starting of macro code and use
ActiveSheet.Protect Password:="1234" at end of code before end sub

but with above coding I am not able to allow my above permission.

Please give me a code to protect sheet again after macro run with allowing Unlocked cell selection, Sorting, allow filtering and allow cell formating.

 

[Keetoowah]

Hi pateltarunt,
in the future please open a new thread for each topic.
Here is the code, from macro recorder, and the link to the site with a reference to all the options of the Protect method:

ActiveSheet.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
    ActiveSheet.EnableSelection = xlUnlockedCells

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect

 

[pateltarunt]

Thanks for reply and solution.
Ok I will post new topic with new name next time.