Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook links between 2 files. Update error | Setting up and Configuration of Excel | |||
merging files | New Users to Excel | |||
merging single worksheet files into a single workbook | Excel Discussion (Misc queries) | |||
merging zip files! | Excel Discussion (Misc queries) | |||
merging different workbook files | Excel Worksheet Functions |