CODEWITHSUNDEEP
vbaexcel
Neithan
Neithan

Reputation: 31

Open folder with VBA

Can anybody help me with this code?
How can I open the existing folder?

Sub click button()
    Dim folderpath As String
    folderpath = "c:\"
    Dim rng As Range
    Set rng = Range(Selection.Address)
    Dim col As Range

    For Each col In rng.Rows
        If Dir(folderpath + CStr(col.Rows), vbDirectory) = "" Then
            Dim response
            response = MsgBox("Folder:" & col.Rows & " doesnt exist. Do you want to create it?", vbYesNo, "Folder")
            If response = vbYes Then
                MkDir (folderpath + CStr(col.Rows))
            End If 
        Else
            MsgBox "Folder:" & col.Rows & " exists"
        End If
    Next col
End Sub

Upvotes: 1

Views: 5776

Answers (1)

Absinthe
Absinthe

Reputation: 3391

Use the Shell function:

Shell "C:\WINDOWS\explorer.exe """ & folderpath + CStr(col.Rows) & "", vbNormalFocus

In full:

Sub click button()
    Dim folderpath As String
    folderpath = "c:\"
    Dim rng As Range
    Set rng = Range(Selection.Address)
    Dim col As Range

    For Each col In rng.Rows
        If Dir(folderpath + CStr(col.Rows), vbDirectory) = "" Then
            Dim response
            response = MsgBox("Folder:" & col.Rows & " doesnt exist. Do you want to create it?", vbYesNo, "Folder")
            If response = vbYes Then
                MkDir (folderpath + CStr(col.Rows))
            End If 
        Else
            response = MsgBox("Folder:" & col.Rows & " exists. Do you want to open it?", vbYesNo, "Folder")
            If response = vbYes Then
                 Shell "C:\WINDOWS\explorer.exe """ & folderpath + CStr(col.Rows) & "", vbNormalFocus
            End if
        End If
    Next col
End Sub

Upvotes: 3

Related Questions