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)

 

[Harlan Grove]

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.

....

Which version of Windows?

Would there be one folder name per line in the list?

Would all folders be created in the current directory?

If the answers to both the last two questions were yes, then


Sub mkd()
'assumes selected range contains folder names, one per row in 1st col
Dim i As Long
If Not TypeOf Selection Is Range Then Exit Sub
With Selection
For i = 1 To .Rows.Count
MkDir .Cells(i, 1).Value
Next i
End With
End Sub

 

[BOB]

There was an error. It went like this:

Run-time error '76':
Path not found

And to answer your questions I am using Win XP and I would like to create
the folders based on the contents of the cell being the name of the folder
and subfolders being one cell to the right and one cell below like this:

Folder1
Folder2
Subfolder1
SubfolderA
SubfolderB
Subfolder2
Subfolder3
Folder 3
Subfolder1
SubfolderA
....And so on

Thanks in advance

P.S. This newsgroup has been the best thing that I have found on the web
since I started using it!

 

[Frank Kabel]

Hi Bob
try the following code. To use this macro do the following:
- Select your directory name area
- invoke the macro 'start_folder_create'
- Enter your start directory (format: 'C:\Temp' -> without trailing
'\')
That's it.

One comment: As I'm a little bit careful the code below just gives you
a messagebox for each folder to be created. Try this out and change the
lines
MsgBox folder_str
'MkDir folder_str
to
'MsgBox folder_str
MkDir folder_str

after you're sure you want to do this

Frank



----
Option Explicit
Sub start_folder_create()
Dim start_folder As String
Dim rng_folder As Range

start_folder = InputBox("Insert your starting folder")
Set rng_folder = Selection
print_folder start_folder, rng_folder
End Sub


Sub print_folder(start_dir As String, search_rng As Range)
Dim row_index As Integer
Dim folder_str As String
Dim new_range As Range
Dim new_rows As Integer

folder_str = start_dir & "\"
With search_rng
For row_index = 1 To .Rows.Count
If .Cells(row_index, 1).Value <> "" Then
folder_str = folder_str & .Cells(row_index, 1).Value
MsgBox folder_str
'MkDir folder_str
If .Columns.Count > 1 And row_index < .Rows.Count Then
new_rows = row_index
While .Cells(new_rows + 1, 1).Value = "" And new_rows <
..Rows.Count
new_rows = new_rows + 1
Wend
If new_rows > row_index Then
Set new_range = .Cells(row_index + 1,
2).Resize(new_rows - row_index, .Columns.Count - 1)
print_folder folder_str, new_range
End If
End If
End If
folder_str = start_dir & "\"
Next row_index
End With
End Sub