G
Guest
I am currently using the following code. I am trying to find some
additional code that will go to a designated folder and open the most recent
excel file and run the remaining syntax. So this new code would go to the
macros folder and find the most recent quote sheet1.xls and open it and run
the remainder of the macro it would do the same for the remaining quote
sheet files. If I had to save the quote sheet test1, test2, test3 into
separate folders I can do that as well.
Any help would be greatly appreciated.
Sub aaa()
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test1.xls"
Workbooks("quote sheet test1.xls").Activate
'nominate the output file
Set OutFile = Workbooks("results of running macro.xls").Sheets("sheet2")
OutPutRow = 1 'nominate the starting row
Workbooks("quote sheet test1.xls").Activate 'go to the first sample file
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
first file
OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
1 'Range("a65536").End(xlUp).Row + 1 'update the output row based on the
data in the first file
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test2.xls"
Workbooks("quote sheet test2.xls").Activate
Workbooks("quote sheet test2.xls").Activate
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
second file
OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
1 'update the output row based on the data in the second file
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test3.xls"
Workbooks("quote sheet test3.xls").Activate
Workbooks("quote sheet test3.xls").Activate
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
second file
End Sub
Regards,
Judd
additional code that will go to a designated folder and open the most recent
excel file and run the remaining syntax. So this new code would go to the
macros folder and find the most recent quote sheet1.xls and open it and run
the remainder of the macro it would do the same for the remaining quote
sheet files. If I had to save the quote sheet test1, test2, test3 into
separate folders I can do that as well.
Any help would be greatly appreciated.
Sub aaa()
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test1.xls"
Workbooks("quote sheet test1.xls").Activate
'nominate the output file
Set OutFile = Workbooks("results of running macro.xls").Sheets("sheet2")
OutPutRow = 1 'nominate the starting row
Workbooks("quote sheet test1.xls").Activate 'go to the first sample file
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
first file
OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
1 'Range("a65536").End(xlUp).Row + 1 'update the output row based on the
data in the first file
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test2.xls"
Workbooks("quote sheet test2.xls").Activate
Workbooks("quote sheet test2.xls").Activate
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
second file
OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
1 'update the output row based on the data in the second file
Workbooks.Open Filename:="C:\Documents and Settings\Default\My
Documents\macros\quote sheet test3.xls"
Workbooks("quote sheet test3.xls").Activate
Workbooks("quote sheet test3.xls").Activate
Range("a1:" &
Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
second file
End Sub
Regards,
Judd