Error when merging files into new WorkBook
 

I am currently running a macro from another workbook and asking it to
add a new workbook. It adds the new workbook fine and copies all the
data into the new workbook. After it has done this an error pops up
and highlights

Set WB = Workbooks.Open(FName)

What am I doing wrong that is causing this to happen?

Sub Summary()

Application.ScreenUpdating = False
Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Excel Test"
ChDrive FOLDERNAME
ChDir FOLDERNAME

Workbooks.Add
Set Dest = Range("A2")
FName = Dir("*.xls")

Do Until FName = "stop.xls"
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Excel Test\Summary - " &
Format(Date, "mm-dd-yyyy") & ".xls"
ActiveWorkbook.Close

End Sub

Any advise is greatly appreachiated!!:confused:


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

Error when merging files into new WorkBook
 

I'm guessing that there are no more files in that directory to process and
that FName = ""

Try putting the following before the line that errors:
MsgBox FName


Rob


"rglasunow " wrote in message
...
I am currently running a macro from another workbook and asking it to
add a new workbook. It adds the new workbook fine and copies all the
data into the new workbook. After it has done this an error pops up
and highlights

Set WB = Workbooks.Open(FName)

What am I doing wrong that is causing this to happen?

Sub Summary()

Application.ScreenUpdating = False
Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Excel Test"
ChDrive FOLDERNAME
ChDir FOLDERNAME

Workbooks.Add
Set Dest = Range("A2")
FName = Dir("*.xls")

Do Until FName = "stop.xls"
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Excel Test\Summary - " &
Format(Date, "mm-dd-yyyy") & ".xls"
ActiveWorkbook.Close

End Sub

Any advise is greatly appreachiated!!:confused:


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

Error when merging files into new WorkBook
 

Thanks for the tip. However, it didn't exactly work for me. The onl
thing it did differently was bring up a msgbox each time it opened
file. When it got to the end it still highlighted the same line o
code.

It brings everything over fine and even saves the file but it jus
doesn't seem to know what to do at the end.

Any sugestions would be great.
thanks

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

Error when merging files into new WorkBook
 

FYI...
I found that if I just take name of the file to stop at out of the cod
it works perfectly fine.

Do Until FName = ""
versus
Do Until FName = "stop.xls"

Thanks

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