Importing

[BOB]

Hi all,
I posted this question earlier but haven't been able to make it work for me.

Please help me figure a way to import hundreds
of folder names from Explorer into Excel without doing it individually
(that's a lot of copy-paste!)

I have been advised to create a file from the command prompt, but it has a
lot of garbage to filter through.
I was also told to write code in this form but have no clue how to use it or
don't know where to run it:

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(1, 0)
arFiles(0, 0) = "C:\myTest"
arFiles(1, 0) = level
SelectFiles "C:\myTest"

cnt = 0
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
ActiveSheet.Cells(i + 1, arFiles(1, i)).Value = arFiles(0, i)
Next

End Sub

'----------------------------------------------------------------------
-----
----
Sub SelectFiles(sPath)
'----------------------------------------------------------------------
-----
----
Dim fldr As Object
Dim Folder As Object

Set Folder = FSO.Getfolder(sPath)
level = level + 1
For Each fldr In Folder.Subfolders
cnt = cnt + 1
ReDim Preserve arFiles(1, cnt)
arFiles(0, cnt) = fldr.Name
arFiles(1, cnt) = level
SelectFiles fldr.Path
level = level - 1
Next

End Sub

Ideally, I would like to be able to take each sub folder name from a folder
and place each name into a cell in excel without having to go through and
delete unwanted things.

If somebody could please give me a step by step approach to solving my
problem, my life would be much better, and it would be very very
appreciated.

Or, if you could, email me at (e-mail address removed) so that I can have a
conversation with you. That tends to simplify things.

Thanks in advance
(Thanks Frank Kabel for the code)

 

[Frank Kabel]

Hi Bob
thirst you should thank Bob Williams for the code (I justed reposted
his part) . For the usage of this code, do the following:
1. Open a new workbook. Hit ALT F11 to open the VBA editor
2. Paste the code (eliminate text comments and have a look for word
wrapping at the end of a line). Change the starting folder in this
macro to your desired folder
3. Close the VBA editor
4. Save the workbook
5. Goto 'Tools - Macro' and Start the macro 'Folders'

This should do. for more information regarding macros look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm)

Frank

 

[Frank Kabel]

Hi Bob
thirst you should thank Bob Williams for the code (I justed reposted

Bob Phillips, of course (its getting late, sorry for that, Bob)

Frank

 

[Max]

Here's some steps to ease you in

Press Alt + F11 to go to VBE

Click Insert > Module

Copy > Paste everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side

Press Alt + Q to exit and return to Excel

Now to trial-run the macro

In a *new* sheet

Click on say, cell A1

Press Alt + F8
(this brings up the "Macro" dialog)

Click on the macro "Folders" > Run
(or just double click on "Folders")

You'll be prompted* to input a folder path** [ twice ].

*I've just dropped an Inputbox or 2 into Bob Phillip's code
to make it easier for us to enter the folder path <g>

**Ensure the folder exists

-----------begin vba--------
Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles
Sub Folders()
Dim i As Long
'by Bob Phillips
'Lists Folders

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(1, 0)
arFiles(0, 0) = InputBox("Enter Path, e.g.: D:\Test")
arFiles(1, 0) = level
SelectFiles InputBox("Re-enter the path")

cnt = 0
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
ActiveSheet.Cells(i + 1, arFiles(1, i)).Value = arFiles(0, i)
Next

End Sub

Sub SelectFiles(sPath)
Dim fldr As Object
Dim Folder As Object

Set Folder = FSO.Getfolder(sPath)
level = level + 1
For Each fldr In Folder.Subfolders
cnt = cnt + 1
ReDim Preserve arFiles(1, cnt)
arFiles(0, cnt) = fldr.Name
arFiles(1, cnt) = level
SelectFiles fldr.Path
level = level - 1
Next

End Sub
------------- end vba------------

 

[BOB]

Ok, you guys are good- that worked like a charm. Can you reverse this
process to create folders from a list in excel or some other text software?
I have asked many people in the past but nobody ever had an answer. I can
use this for many things.

Here's some steps to ease you in

Press Alt + F11 to go to VBE

Click Insert > Module

Copy > Paste everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side

Press Alt + Q to exit and return to Excel

Now to trial-run the macro

In a *new* sheet

Click on say, cell A1

Press Alt + F8
(this brings up the "Macro" dialog)

Click on the macro "Folders" > Run
(or just double click on "Folders")

You'll be prompted* to input a folder path** [ twice ].

*I've just dropped an Inputbox or 2 into Bob Phillip's code
to make it easier for us to enter the folder path <g>

**Ensure the folder exists

-----------begin vba--------
Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles
Sub Folders()
Dim i As Long
'by Bob Phillips
'Lists Folders

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(1, 0)
arFiles(0, 0) = InputBox("Enter Path, e.g.: D:\Test")
arFiles(1, 0) = level
SelectFiles InputBox("Re-enter the path")

cnt = 0
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
ActiveSheet.Cells(i + 1, arFiles(1, i)).Value = arFiles(0, i)
Next

End Sub

Sub SelectFiles(sPath)
Dim fldr As Object
Dim Folder As Object

Set Folder = FSO.Getfolder(sPath)
level = level + 1
For Each fldr In Folder.Subfolders
cnt = cnt + 1
ReDim Preserve arFiles(1, cnt)
arFiles(0, cnt) = fldr.Name
arFiles(1, cnt) = level
SelectFiles fldr.Path
level = level - 1
Next

End Sub
------------- end vba------------

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

Hi all,
I posted this question earlier but haven't been able to make it work for

m

e.

Please help me figure a way to import hundreds
of folder names from Explorer into Excel without doing it individually
(that's a lot of copy-paste!)

I have been advised to create a file from the command prompt, but it has a
lot of garbage to filter through.
I was also told to write code in this form but have no clue how to use

it

or
don't know where to run it:

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(1, 0)
arFiles(0, 0) = "C:\myTest"
arFiles(1, 0) = level
SelectFiles "C:\myTest"

cnt = 0
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
ActiveSheet.Cells(i + 1, arFiles(1, i)).Value = arFiles(0, i)
Next

End Sub

'----------------------------------------------------------------------
-----
----
Sub SelectFiles(sPath)
'----------------------------------------------------------------------
-----
----
Dim fldr As Object
Dim Folder As Object

Set Folder = FSO.Getfolder(sPath)
level = level + 1
For Each fldr In Folder.Subfolders
cnt = cnt + 1
ReDim Preserve arFiles(1, cnt)
arFiles(0, cnt) = fldr.Name
arFiles(1, cnt) = level
SelectFiles fldr.Path
level = level - 1
Next

End Sub

Ideally, I would like to be able to take each sub folder name from a folder
and place each name into a cell in excel without having to go through and
delete unwanted things.

If somebody could please give me a step by step approach to solving my
problem, my life would be much better, and it would be very very
appreciated.

Or, if you could, email me at (e-mail address removed) so that I can have a
conversation with you. That tends to simplify things.

Thanks in advance
(Thanks Frank Kabel for the code)