thanx for the support. I copied/pasted in module 1 but get a compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name of another
file? I t
looks like it's using itself and creating a data page (which is fine). Am I
reading this right? How can I point it to the drive I want it to look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy, find the
next
open row, record all the file names on that floppy (column A) next
row, then
record all the sheet names of that file in (columns B through
whatever is
needed). I would then put the next floppy in and the macro would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A drive, find
all file
names into variables, then find all sheet names in each file and
record the
results in another file that will keep expanding until all floppies
are read.

I do not know how many floppies there might be (eventually there
could be
600). The number of files/floppy will vary (but will usually be
about 4).
The numbers of sheets/file will vary (but will usually be about 3).

One or two errors in my code - fixed and tested.
Below is the entire code - complete with Bill Manville's code from the
link I posted (slightly modified).
Put all of this into a module in a new workbook, and name one of the
sheets in the book "Data".

You would be well-served by following Don's avice to copy the files to
your hard drive rather than working from the floppies.

If you still have problems then you can contact me via email (fix my
address first...)

Cheers,
Tim.




Option Explicit

'Bill Manville's code
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

stDirectory = "A:\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub


'Tims code
Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(1).Value = wb.Name
.Cells(2).Value = s.Name
End With
i = i + 1
Next s

End Sub

"bcnu" wrote in message
...
thanx for the support. I copied/pasted in module 1 but get a
compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name of
another
file? I t
looks like it's using itself and creating a data page (which is
fine). Am I
reading this right? How can I point it to the drive I want it to
look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy, find
the
next
open row, record all the file names on that floppy (column A)
next
row, then
record all the sheet names of that file in (columns B through
whatever is
needed). I would then put the next floppy in and the macro would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A drive,
find
all file
names into variables, then find all sheet names in each file and
record the
results in another file that will keep expanding until all
floppies
are read.

I do not know how many floppies there might be (eventually there
could be
600). The number of files/floppy will vary (but will usually be
about 4).
The numbers of sheets/file will vary (but will usually be about
3).

Thank you. I replied to Don that I will follow his guidance and work from
the hard drive. I now have all the floppies copied to a single directory on
my hard drive with each of them in their own sub-directory. I assume I'll
have to adjust your code slightly, but I think I can do that. I could not
have created the code you did so I definately appreciate your help.

"Tim Williams" wrote:

One or two errors in my code - fixed and tested.
Below is the entire code - complete with Bill Manville's code from the
link I posted (slightly modified).
Put all of this into a module in a new workbook, and name one of the
sheets in the book "Data".

You would be well-served by following Don's avice to copy the files to
your hard drive rather than working from the floppies.

If you still have problems then you can contact me via email (fix my
address first...)

Cheers,
Tim.




Option Explicit

'Bill Manville's code
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

stDirectory = "A:\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub


'Tims code
Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(1).Value = wb.Name
.Cells(2).Value = s.Name
End With
i = i + 1
Next s

End Sub

"bcnu" wrote in message
...
thanx for the support. I copied/pasted in module 1 but get a
compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name of
another
file? I t
looks like it's using itself and creating a data page (which is
fine). Am I
reading this right? How can I point it to the drive I want it to
look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy, find
the
next
open row, record all the file names on that floppy (column A)
next
row, then
record all the sheet names of that file in (columns B through
whatever is
needed). I would then put the next floppy in and the macro would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A drive,
find
all file
names into variables, then find all sheet names in each file and
record the
results in another file that will keep expanding until all
floppies
are read.

I do not know how many floppies there might be (eventually there
could be
600). The number of files/floppy will vary (but will usually be
about 4).
The numbers of sheets/file will vary (but will usually be about
3).

I've tried, but I'm not real good with arrays (yet). In following Don's
advice, I have two issues I could use some help with to modify the code you
provided.

I created a directory on my desktop called structure
Some of the floppies had directories on them which means I have
sub-directories under the Structure directory now.

I've modified your code to find the structure directory on my hard drive
(which is where I loaded each of the 15 floppies to); and it finds the
structre directory, first file and all sheets in that first file perfectly.

I'm not sure how to modify the code (arrays) to add more directorys (the
other 14 floppies worth of fiels) in the structure file (the Structure
directory name would go in column a).

If that structure directory has a sub-directory that name would go in column
B (blank otherwise).

then the file name in column C (I've made this change, it was going in
column a)

then the sheet name in column d (I've made this change, it was going in
column b)

The code now looks like:

Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim aFilename() As String, iFilename As Integer
Dim stFile As String, vFile As Variant
Dim stFilename As String, vFilename As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

'Clear the old results
Sheets("Data").Select
Cells.Select
Selection.Clear
Range("A1").Select
'Set up Column Headers
Cells(1, 1) = "Directory"
Cells(1, 2) = "Sub-Directory"
Cells(1, 3) = "Workbook"
Cells(1, 4) = "Worksheet"
' name of directory to look in
stDirectory = "C:\Documents and Settings\gejones\Desktop\Structure files\"
' use Dir function to find XLS files in Directory
stFilename = CurDir & "\"
stFile = Dir(stFilename & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stFilename & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub

Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(3).Value = wb.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

End Sub


"bcnu" wrote:

Thank you. I replied to Don that I will follow his guidance and work from
the hard drive. I now have all the floppies copied to a single directory on
my hard drive with each of them in their own sub-directory. I assume I'll
have to adjust your code slightly, but I think I can do that. I could not
have created the code you did so I definately appreciate your help.

"Tim Williams" wrote:

One or two errors in my code - fixed and tested.
Below is the entire code - complete with Bill Manville's code from the
link I posted (slightly modified).
Put all of this into a module in a new workbook, and name one of the
sheets in the book "Data".

You would be well-served by following Don's avice to copy the files to
your hard drive rather than working from the floppies.

If you still have problems then you can contact me via email (fix my
address first...)

Cheers,
Tim.




Option Explicit

'Bill Manville's code
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

stDirectory = "A:\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub


'Tims code
Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(1).Value = wb.Name
.Cells(2).Value = s.Name
End With
i = i + 1
Next s

End Sub

"bcnu" wrote in message
...
thanx for the support. I copied/pasted in module 1 but get a
compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name of
another
file? I t
looks like it's using itself and creating a data page (which is
fine). Am I
reading this right? How can I point it to the drive I want it to
look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy, find
the
next
open row, record all the file names on that floppy (column A)
next
row, then
record all the sheet names of that file in (columns B through
whatever is
needed). I would then put the next floppy in and the macro would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A drive,
find
all file
names into variables, then find all sheet names in each file and
record the
results in another file that will keep expanding until all
floppies
are read.

I do not know how many floppies there might be (eventually there
could be
600). The number of files/floppy will vary (but will usually be
about 4).
The numbers of sheets/file will vary (but will usually be about
3).

If you need to search subfolders then its easier to use "filesearch"
than to code the recursive routine.

Tim.


Option Explicit

Sub ProcessAll()

'adjust your path to suit
Const sPath As String = "C:\Analysis\test\"

Dim wb As Workbook, i As Integer

With ThisWorkbook.Sheets("Data")
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "Workbook"
.Cells(1, 4) = "Worksheet"
End With

With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"

If .Execute() Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
ProcessWorkbook wb
wb.Close SaveChanges:=False
Next i
End If
End With

End Sub

Sub ProcessWorkbook(oWB As Workbook)
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In oWB.Worksheets
With d.Rows(i)
.Cells(1).Value = oWB.Path
.Cells(2).Value = "" 'you can get the folder name from the
path
.Cells(3).Value = oWB.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

End Sub










"bcnu" wrote in message
...
I've tried, but I'm not real good with arrays (yet). In following
Don's
advice, I have two issues I could use some help with to modify the
code you
provided.

I created a directory on my desktop called structure
Some of the floppies had directories on them which means I have
sub-directories under the Structure directory now.

I've modified your code to find the structure directory on my hard
drive
(which is where I loaded each of the 15 floppies to); and it finds
the
structre directory, first file and all sheets in that first file
perfectly.

I'm not sure how to modify the code (arrays) to add more directorys
(the
other 14 floppies worth of fiels) in the structure file (the
Structure
directory name would go in column a).

If that structure directory has a sub-directory that name would go
in column
B (blank otherwise).

then the file name in column C (I've made this change, it was going
in
column a)

then the sheet name in column d (I've made this change, it was going
in
column b)

The code now looks like:

Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim aFilename() As String, iFilename As Integer
Dim stFile As String, vFile As Variant
Dim stFilename As String, vFilename As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

'Clear the old results
Sheets("Data").Select
Cells.Select
Selection.Clear
Range("A1").Select
'Set up Column Headers
Cells(1, 1) = "Directory"
Cells(1, 2) = "Sub-Directory"
Cells(1, 3) = "Workbook"
Cells(1, 4) = "Worksheet"
' name of directory to look in
stDirectory = "C:\Documents and
Settings\gejones\Desktop\Structure files\"
' use Dir function to find XLS files in Directory
stFilename = CurDir & "\"
stFile = Dir(stFilename & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stFilename & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub

Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(3).Value = wb.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

End Sub


"bcnu" wrote:

Thank you. I replied to Don that I will follow his guidance and
work from
the hard drive. I now have all the floppies copied to a single
directory on
my hard drive with each of them in their own sub-directory. I
assume I'll
have to adjust your code slightly, but I think I can do that. I
could not
have created the code you did so I definately appreciate your help.

"Tim Williams" wrote:

One or two errors in my code - fixed and tested.
Below is the entire code - complete with Bill Manville's code
from the
link I posted (slightly modified).
Put all of this into a module in a new workbook, and name one of
the
sheets in the book "Data".

You would be well-served by following Don's avice to copy the
files to
your hard drive rather than working from the floppies.

If you still have problems then you can contact me via email (fix
my
address first...)

Cheers,
Tim.




Option Explicit

'Bill Manville's code
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a
file

stDirectory = "A:\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub


'Tims code
Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(1).Value = wb.Name
.Cells(2).Value = s.Name
End With
i = i + 1
Next s

End Sub

"bcnu" wrote in message
...
thanx for the support. I copied/pasted in module 1 but get a
compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have
some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name
of
another
file? I t
looks like it's using itself and creating a data page (which is
fine). Am I
reading this right? How can I point it to the drive I want it
to
look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy,
find
the
next
open row, record all the file names on that floppy (column
A)
next
row, then
record all the sheet names of that file in (columns B
through
whatever is
needed). I would then put the next floppy in and the macro
would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A
drive,
find
all file
names into variables, then find all sheet names in each file
and
record the
results in another file that will keep expanding until all
floppies
are read.

I do not know how many floppies there might be (eventually
there
could be
600). The number of files/floppy will vary (but will
usually be
about 4).
The numbers of sheets/file will vary (but will usually be
about
3).

Thanx again. This works great. I knew I had a lot to learn about arrays,
but I don't even understand this code so I'll try to learn it also.

Thanx again. I appreciate the quick feedback once again. I'm on the road
and been checking between hotel rooms and you're always right there for me.

Happy holidays to you and yours.

"Tim Williams" wrote:

If you need to search subfolders then its easier to use "filesearch"
than to code the recursive routine.

Tim.


Option Explicit

Sub ProcessAll()

'adjust your path to suit
Const sPath As String = "C:\Analysis\test\"

Dim wb As Workbook, i As Integer

With ThisWorkbook.Sheets("Data")
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "Workbook"
.Cells(1, 4) = "Worksheet"
End With

With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"

If .Execute() Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
ProcessWorkbook wb
wb.Close SaveChanges:=False
Next i
End If
End With

End Sub

Sub ProcessWorkbook(oWB As Workbook)
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In oWB.Worksheets
With d.Rows(i)
.Cells(1).Value = oWB.Path
.Cells(2).Value = "" 'you can get the folder name from the
path
.Cells(3).Value = oWB.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

End Sub










"bcnu" wrote in message
...
I've tried, but I'm not real good with arrays (yet). In following
Don's
advice, I have two issues I could use some help with to modify the
code you
provided.

I created a directory on my desktop called structure
Some of the floppies had directories on them which means I have
sub-directories under the Structure directory now.

I've modified your code to find the structure directory on my hard
drive
(which is where I loaded each of the 15 floppies to); and it finds
the
structre directory, first file and all sheets in that first file
perfectly.

I'm not sure how to modify the code (arrays) to add more directorys
(the
other 14 floppies worth of fiels) in the structure file (the
Structure
directory name would go in column a).

If that structure directory has a sub-directory that name would go
in column
B (blank otherwise).

then the file name in column C (I've made this change, it was going
in
column a)

then the sheet name in column d (I've made this change, it was going
in
column b)

The code now looks like:

Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim aFilename() As String, iFilename As Integer
Dim stFile As String, vFile As Variant
Dim stFilename As String, vFilename As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

'Clear the old results
Sheets("Data").Select
Cells.Select
Selection.Clear
Range("A1").Select
'Set up Column Headers
Cells(1, 1) = "Directory"
Cells(1, 2) = "Sub-Directory"
Cells(1, 3) = "Workbook"
Cells(1, 4) = "Worksheet"
' name of directory to look in
stDirectory = "C:\Documents and
Settings\gejones\Desktop\Structure files\"
' use Dir function to find XLS files in Directory
stFilename = CurDir & "\"
stFile = Dir(stFilename & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stFilename & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub

Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(3).Value = wb.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

End Sub


"bcnu" wrote:

Thank you. I replied to Don that I will follow his guidance and
work from
the hard drive. I now have all the floppies copied to a single
directory on
my hard drive with each of them in their own sub-directory. I
assume I'll
have to adjust your code slightly, but I think I can do that. I
could not
have created the code you did so I definately appreciate your help.

"Tim Williams" wrote:

One or two errors in my code - fixed and tested.
Below is the entire code - complete with Bill Manville's code
from the
link I posted (slightly modified).
Put all of this into a module in a new workbook, and name one of
the
sheets in the book "Data".

You would be well-served by following Don's avice to copy the
files to
your hard drive rather than working from the floppies.

If you still have problems then you can contact me via email (fix
my
address first...)

Cheers,
Tim.




Option Explicit

'Bill Manville's code
Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String
Dim stCode As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a
file

stDirectory = "A:\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.xls")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
ProcessWorkbook ActiveWorkbook
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub


'Tims code
Sub ProcessWorkbook(oWB As Workbook)

Dim wb As Workbook
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

Set wb = ActiveWorkbook
i = d.Cells(d.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each s In wb.Worksheets
With d.Rows(i)
.Cells(1).Value = wb.Name
.Cells(2).Value = s.Name
End With
i = i + 1
Next s

End Sub

"bcnu" wrote in message
...
thanx for the support. I copied/pasted in module 1 but get a
compiler error
on with d.row(i). Is there a chance this should be rows?

You are using levels of Macro I've never used before so I have
some
ignorance questions, if you have time.

is this macro set up to look at the workbook and worksheet name
of
another
file? I t
looks like it's using itself and creating a data page (which is
fine). Am I
reading this right? How can I point it to the drive I want it
to
look at etc?

The rest I'll play with when I get past the compiler error.

Thanx again, I truly appreciate it.
"Tim Williams" wrote:

See this for finding all files

http://groups.google.com/groups?q=di... n.net&rnum=2

then try this to process each workbook

sub ProcessActiveWorkbook()

dim wb as workbook
dim i
dim s as worksheet
dim d as worksheet

set d=thisworkbook.worksheets("Data")
'sheet data has "filename" in A1, "sheetname" in B1

set wb =activeworkbook
i=d.cells(1,d.rows.count).end(xlup).offset(1,0).ro w

for each s in wb.worksheets
with d.row(i)
.cells(1).value=wb.name
.cells(2).value=s.name
end with
i=i+1
next s

end sub

(untested but should more or less work)

Tim


"bcnu" wrote in message
...
Looking to make a macro in one file that can open a floppy,
find
the
next
open row, record all the file names on that floppy (column
A)
next
row, then
record all the sheet names of that file in (columns B
through
whatever is
needed). I would then put the next floppy in and the macro
would
find the
next unused row and so on until I have read all floppies.

In short, I want to run the macro that will open the A
drive,
find
all file
names into variables, then find all sheet names in each file
and
record the
results in another file that will keep expanding until all
floppies
are read.

I do not know how many floppies there might be (eventually
there
could be
600). The number of files/floppy will vary (but will
usually be
about 4).
The numbers of sheets/file will vary (but will usually be
about
3).