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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200810/1

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
--
Gary''s Student - gsnu200810


"Sri via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200810/1

Hello Friend,

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

Have a nice time.

Sri

Gary''s Student wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200810/1