I have this code. It generates a list of files in the c:\ Directory.
then opens these files and copies the information to Book1.xls.

It only does this for the firt file listed. At most there are going t
be 8 xls files in the root directory at all time. I could repeat thi
code 8 time which would be no problem. But If i did this and there wer
only 3 files in the directory, it would come up with an error messag
asking if I want to debug.

Could I therefore use an IF command that states that is ther is no fil
name in Cell A(Whatever) then the macro will stop?

I know its a bit long whinded but any help would be much appreciated!

Sub ListFiles()
Range("A:A").Select
Selection.Delete
Range("A1").Select
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
Range("B1").Select
ActiveCell.FormulaR1C1 = "=""C:\"""
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&RC[-2]"
Workbooks.Open Filename:=Range("C1").Value
ActiveWindow.WindowState = xlNormal
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Workbooks("Book1.xls").Sheets(2)
Sheets("Sheet1").Select
ActiveWindow.WindowState = xlMaximized
MsgBox ("Completed Copying")
End Su

--
Message posted from http://www.ExcelForum.com

Option Explicit
Private root As String
Sub ListFiles()
Dim sFileName As String
Dim index As Long
Dim ws As Worksheet

Set ws = ActiveSheet

root = "C:\"
ws.Range("A:A").Clear

sFileName = Dir(root & "*.XLS")
Do While sFileName < ""
index = index + 1
ws.Cells(index, 1) = sFileName
CopySheets sFileName
sFileName = Dir()
Loop

MsgBox ("Completed Copying")
End Sub
Sub CopySheets(sFileName As String)
Dim wb As Workbook
Set wb = Workbooks.Open(root & sFileName)
ActiveWindow.WindowState = xlNormal
wb.Sheets(1).Copy Befo=ThisWorkbook.Sheets(1)
wb.Close False
End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
I have this code. It generates a list of files in the
c:\ Directory. I
then opens these files and copies the information to
Book1.xls.

It only does this for the firt file listed. At most
there are going to
be 8 xls files in the root directory at all time. I
could repeat this
code 8 time which would be no problem. But If i did this
and there were
only 3 files in the directory, it would come up with an
error message
asking if I want to debug.

Could I therefore use an IF command that states that is
ther is no file
name in Cell A(Whatever) then the macro will stop?

I know its a bit long whinded but any help would be much
appreciated!

Sub ListFiles()
Range("A:A").Select
Selection.Delete
Range("A1").Select
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
Range("B1").Select
ActiveCell.FormulaR1C1 = "=""C:\"""
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&RC[-2]"
Workbooks.Open Filename:=Range("C1").Value
ActiveWindow.WindowState = xlNormal
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Workbooks
("Book1.xls").Sheets(2)
Sheets("Sheet1").Select
ActiveWindow.WindowState = xlMaximized
MsgBox ("Completed Copying")
End Sub


---
Message posted from http://www.ExcelForum.com/

.

Thats great. How did you know all that cool code

--
Message posted from http://www.ExcelForum.com

Would it be possible to make the macro name the sheets
1,2,3,4,5,6,7,8......

How would you then make a macro that delete's the newly copied sheets
from the current worksheet?


---
Message posted from http://www.ExcelForum.com/

change:

index = index + 1
ws.Cells(index, 1) = sFileName
CopySheets sFileName
activesheet.Name = "Sheet_" & Index ''NEW LINE
sFileName = Dir()


The new line simply changes th ecactive sheet's name to
Sheet_nn
where nn is the index numbe. You might want to use a
function for this in case a sheet with the same name
already exists.

Application.DisplayAlerts=False
worksheets(n).Delete
Application.DisplayAlerts=True

where n can be a number or a sheet name

regards

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Would it be possible to make the macro name the sheets
1,2,3,4,5,6,7,8......

How would you then make a macro that delete's the newly
copied sheets
from the current worksheet?


---
Message posted from http://www.ExcelForum.com/

.