Browse Window

[Sri via OfficeKB.com]

Hello all,

Could anybody help me regarding the following issues.

I am writing a VB macro in XL where I need to select a folder by clicking on
the “Open†button, created by me. I should be able to browse my computer to
select a folder and retuen back to my xl sheet with the selected folder path.
Then if I press “Executeâ€, it should execute the DOS command “dir /s > files.
xls†to get all the files including its sub folders in to an xml file. Then I
will apply some simple logic to meet my requirements.

Could anybody provide me the VB code for the following activities.

1. VB code to open a new browse window where I can select a folder and this
path is returned back to text box.

2. A way to invoke the command prompt from VB macro to execute the command
“dir /s > files.xlsâ€

Thanks
Sri

 

[Gary''s Student]

For the folder browser:

Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
' F.Items.Item.InvokeVerb
End If

End Function

Sub Display_Folder()
Dim FName As String
FName = BrowseFolder(Caption:="Select A Folder", InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
MsgBox ("No folder selected.")
Else
MsgBox ("Folder Selected: " & FName)
End If
End Sub

Must first set a reference to Microsoft Shell Controls and Automation.


For the DIR:

Sub Macro1()
x = Shell("cmd.exe /c dir /s > files.xls", 1)
End Sub

 

[Sri via OfficeKB.com]

Hello Friend,

Thank you very much for your reply. It perfectly works for me. Thanks again.

Have a nice time.

Sri

For the folder browser:

Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
' F.Items.Item.InvokeVerb
End If

End Function

Sub Display_Folder()
Dim FName As String
FName = BrowseFolder(Caption:="Select A Folder", InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
MsgBox ("No folder selected.")
Else
MsgBox ("Folder Selected: " & FName)
End If
End Sub

Must first set a reference to Microsoft Shell Controls and Automation.

For the DIR:

Sub Macro1()
x = Shell("cmd.exe /c dir /s > files.xls", 1)
End Sub

Hello all,

[quoted text clipped - 17 lines]

Thanks
Sri