Friday, June 22, 2012

Excel VBA – Use SHELL function to open a folder

I try to make my administrative work fun.

I got a project that I need group more than 40 files together in one big folder. Those 40 files are saved in the different folder but the good thing is that they have been saved based on the same pattern.

Instead of opening folders one by one, I use the shell function.



Shell (pathname [,windowstyle])



Open a folder:

Shell "Explorer.exe " & "Filepath" & foldername, vbNormalFocus

Or Shell "Explorer.exe " & "Filepath" & foldername, 1

Note: The blank behind the Explorer.exe is important.



For example,

Shell "Explorer.exe C:\Development\", vbNormalFocus

Or

Shell "Explorer.exe " & "C:\Development\", 1



The above code opens a folder named Development in the C: Drive.



Open a group of folders:

For example, There are several files in the Development Folder named as 1, 2, 3,…

Sub OpenFolders()

    Dim i As Integer

    For i = 1 To 20

    Shell "Explorer.exe " & "C:\Development\" & CStr(i) & "\", vbNormalFocus

    Next i

End Sub



The above code enables to open 20 folders at the same time.



© Copyright Exceltipsandkeys All Rights Reserved.

No comments:

Post a Comment