I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder contains
multiple(more than 100) number of files (.s3p format) that I want to analyze.

I want to create a macro that will allow user to input the main_folder
directory. Then, the all the subfolder will be listed up on the mainsheet, so
that user can choose which subfolder they want to analyze. (where user can
analyzed 1, 2, more or all the subfolder). Can someone help me on this?

I have tried so many ways, but it doesn't works.

Check out Dir() in the help.

NickHK

"cyzax7" <u22797@uwe wrote in message news:6173e848330a5@uwe...
I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
contains
multiple(more than 100) number of files (.s3p format) that I want to
analyze.

I want to create a macro that will allow user to input the main_folder
directory. Then, the all the subfolder will be listed up on the mainsheet,
so
that user can choose which subfolder they want to analyze. (where user can
analyzed 1, 2, more or all the subfolder). Can someone help me on this?

I have tried so many ways, but it doesn't works.

Hi

Copeid below example on FileDialog from the help text in Excel, this could
be an option for you. Just replace the message box code etc

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub
--
Tony Green


"cyzax7" wrote:

I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder contains
multiple(more than 100) number of files (.s3p format) that I want to analyze.

I want to create a macro that will allow user to input the main_folder
directory. Then, the all the subfolder will be listed up on the mainsheet, so
that user can choose which subfolder they want to analyze. (where user can
analyzed 1, 2, more or all the subfolder). Can someone help me on this?

I have tried so many ways, but it doesn't works.

hi,

It doesn't work. I want to create the macro that display my subfolder. the
problem is, it display only the FILES (for example files of type: sample1.
ppt, sample2.txt and etc ) in the main_folder (not the sub folder that I want)



ADG wrote:
Hi

Copeid below example on FileDialog from the help text in Excel, this could
be an option for you. Just replace the message box code etc

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub
I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder contains
[quoted text clipped - 6 lines]

I have tried so many ways, but it doesn't works.

Hi Cyzax7,

Try:

'=============
Public Sub ListFolders()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Const sStartDir As String = "C\" '<<==== CHANGE

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder("C:\")
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============


---
Regards,
Norman


"cyzax7" <u22797@uwe wrote in message news:6173e848330a5@uwe...
I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
contains
multiple(more than 100) number of files (.s3p format) that I want to
analyze.

I want to create a macro that will allow user to input the main_folder
directory. Then, the all the subfolder will be listed up on the mainsheet,
so
that user can choose which subfolder they want to analyze. (where user can
analyzed 1, 2, more or all the subfolder). Can someone help me on this?

I have tried so many ways, but it doesn't works.

Sub Finddir()
Dim FSO As Object
Dim fDir As Object
Dim fSubDir As Object
Dim i As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fDir = FSO.GetFolder("C:\MyTest")
For Each fSubDir In fDir.SubFolders
i = i + 1
Cells(i, "A").Value = fSubDir.Name
Next fSubDir
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cyzax7" <u22797@uwe wrote in message news:6173e848330a5@uwe...
I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
contains
multiple(more than 100) number of files (.s3p format) that I want to
analyze.

I want to create a macro that will allow user to input the main_folder
directory. Then, the all the subfolder will be listed up on the mainsheet,
so
that user can choose which subfolder they want to analyze. (where user can
analyzed 1, 2, more or all the subfolder). Can someone help me on this?

I have tried so many ways, but it doesn't works.

Hi,

In the code below, the directory is set in the macro. How should I make the
macro read a user defined directory which is entered in excel.
What I mean is:

1. In the mainsheet (Excel), user will enter the directory for the
main_folder.

2.Then , once user click on the "Search" button, it will list all the
subfolder (from the main_folder directory) in the mainsheet at a specified
row and column.Let say row 4, column "C".

Norman Jones wrote:
Hi Cyzax7,

Try:

'=============
Public Sub ListFolders()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Const sStartDir As String = "C\" '<<==== CHANGE

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder("C:\")
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============

---
Regards,
Norman

I have a main_folder (I name it as Folder_A) which contains many subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
[quoted text clipped - 9 lines]

I have tried so many ways, but it doesn't works.

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

Hi Cyzax,

Try
'=============
Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============


---
Regards,
Norman



"cyzax7 via OfficeKB.com" <u22797@uwe wrote in message
news:618029ac90469@uwe...
Hi,

In the code below, the directory is set in the macro. How should I make
the
macro read a user defined directory which is entered in excel.
What I mean is:

1. In the mainsheet (Excel), user will enter the directory for the
main_folder.

2.Then , once user click on the "Search" button, it will list all the
subfolder (from the main_folder directory) in the mainsheet at a specified
row and column.Let say row 4, column "C".

Norman Jones wrote:
Hi Cyzax7,

Try:

'=============
Public Sub ListFolders()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Const sStartDir As String = "C\" '<<==== CHANGE

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder("C:\")
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============

---
Regards,
Norman

I have a main_folder (I name it as Folder_A) which contains many
subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
[quoted text clipped - 9 lines]

I have tried so many ways, but it doesn't works.

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

Hi Norman,

I've tried the code and its work. I have created a CommandButton that will
direct to the ListFolders2.How should I modify the code so that the
CommandButton and the listed subfolder will display on the same page (let say,
in my sheet1 which I called as "Home").
I've tried to modify the code, but nothing shows up.
I'm very new to vba programming. ;(


Norman Jones wrote:
Hi Cyzax,

Try
'=============
Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============

---
Regards,
Norman

Hi,

[quoted text clipped - 56 lines]

I have tried so many ways, but it doesn't works.

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

Hi Norman,

I've tried the code and its work. I have created a CommandButton that will
direct to the ListFolders2.How should I modify the code so that the
CommandButton and the listed subfolder will display on the same page (let say,

in my sheet1 which I called as "Home").
I've tried to modify the code, but nothing shows up.
I'm very new to vba programming. ;(


- Cyzax -

Norman Jones wrote:
Hi Cyzax,

Try
'=============
Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============

---
Regards,
Norman

Hi,

[quoted text clipped - 56 lines]

I have tried so many ways, but it doesn't works.

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

Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Home"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Set SH = Worksheets(sName)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

Set SH = Nothing

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Norman Jones" wrote in message
...
Hi Cyzax,

Try
'=============
Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============


---
Regards,
Norman



"cyzax7 via OfficeKB.com" <u22797@uwe wrote in message
news:618029ac90469@uwe...
Hi,

In the code below, the directory is set in the macro. How should I make
the
macro read a user defined directory which is entered in excel.
What I mean is:

1. In the mainsheet (Excel), user will enter the directory for the
main_folder.

2.Then , once user click on the "Search" button, it will list all the
subfolder (from the main_folder directory) in the mainsheet at a
specified
row and column.Let say row 4, column "C".

Norman Jones wrote:
Hi Cyzax7,

Try:

'=============
Public Sub ListFolders()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Folder List"
Const sStartDir As String = "C\" '<<==== CHANGE

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets(sName).Delete
Application.DisplayAlerts = True

Set SH = ThisWorkbook.Sheets. _
Add(after:=Sheets(Sheets.Count))
SH.Name = sName

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder("C:\")
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

End Sub
'<<=============

---
Regards,
Norman

I have a main_folder (I name it as Folder_A) which contains many
subfolder
(Folder_1, Folder_2,Folder_3, ...Folder_n). where in each subfolder
[quoted text clipped - 9 lines]

I have tried so many ways, but it doesn't works.

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

Hi,

I' ve solved the prob...
thanxs... ;

Bob Phillips wrote:
Public Sub ListFolders2()
Dim SH As Worksheet
Dim FSO As Object
Dim StartFolder As Object
Dim Fldr As Object
Dim iCtr As Long
Const sName As String = "Home"
Dim sStartDir As String

sStartDir = InputBox( _
Prompt:="Enter the initial directory", _
Default:="C:\")

On Error Resume Next
Set SH = Worksheets(sName)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set StartFolder = FSO.GetFolder(sStartDir)
SH.Cells(1, "A").Value = sStartDir
For Each Fldr In StartFolder.SubFolders
iCtr = iCtr + 1
SH.Cells(iCtr + 1, "A").Value = Fldr.Name
Next Fldr

SH.Columns("A").AutoFit

Set SH = Nothing

End Sub

Hi Cyzax,

[quoted text clipped - 99 lines]

I have tried so many ways, but it doesn't works.

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