macro code to open most recent file.

[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

 

[Jim Cone]

judd,

You can't have more than one file with the same name
in the same folder. So any file in a folder is the most recent.
The following code opens and closes the files, but you
have to add the code to massage the files while they are open.

Regards,
Jim Cone
San Francisco, USA

'---------------------------------
Sub ChangeDataInFiles()
'Jim Cone - San Francisco, USA - Jun 09, 2005
'*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
'Opens/closes files in the specified folder that have a numeric suffix
'that falls within a specified range.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim lngCount As Long
Dim lngNum As Long
Dim strPath As String
'Specify the base file name.
Const strName As String = "quote sheet test"

'Specify the folder...
strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"
'Establish object references
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
'first numeric suffix to look for
lngCount = 1

For Each objFile In objFolder.Files
lngNum = lngCount

Do
If objFile.Name = strName & lngNum & ".xls" Then
Workbooks.Open strPath & objFile.Name

'*Insert code to do stuff to workbook*

Workbooks(objFile.Name).Close savechanges:=True 'or False
lngCount = lngCount + 1
Exit Do
Else
lngNum = lngNum = 1
End If
Loop While lngNum < 4 'One more than the number of files

If lngCount > 3 Then Exit For 'The number of files to open

Next 'objFile

Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'----------------------------------


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

 

[Jim Cone]

Correction...
lngNum = lngNum = 1

should be
lngNum = lngNum + 1

(my test code and the posted code differed)

Jim Cone


judd,
You can't have more than one file with the same name
in the same folder. So any file in a folder is the most recent.
The following code opens and closes the files, but you
have to add the code to massage the files while they are open.
Regards,
Jim Cone
San Francisco, USA


'---------------------------------
Sub ChangeDataInFiles()
'Jim Cone - San Francisco, USA - Jun 09, 2005
'*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
'Opens/closes files in the specified folder that have a numeric suffix
'that falls within a specified range.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim lngCount As Long
Dim lngNum As Long
Dim strPath As String
'Specify the base file name.
Const strName As String = "quote sheet test"

'Specify the folder...
strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"
'Establish object references
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
'first numeric suffix to look for
lngCount = 1

For Each objFile In objFolder.Files
lngNum = lngCount

Do
If objFile.Name = strName & lngNum & ".xls" Then
Workbooks.Open strPath & objFile.Name

'*Insert code to do stuff to workbook*

Workbooks(objFile.Name).Close savechanges:=True 'or False
lngCount = lngCount + 1
Exit Do
Else
lngNum = lngNum = 1'<<<<change =1 to + 1
End If
Loop While lngNum < 4 'One more than the number of files

If lngCount > 3 Then Exit For 'The number of files to open

Next 'objFile

Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'----------------------------------

 

[Guest]

Jim,
Thank you very much for your patience as I am a new to VBA. I have
a couple of questions.

Where should I put this code that you wrote? Prior to each file opening?

"but you have to add the code to massage the files while they are open." I
am not quite sure what you mean here. Can you please elaborate.


Thank You,


Judd

 

[Jim Cone]

Judd,

'I have included the code to "massage" the files.
'It should be complete? Just run it instead of your code.
'Try it on a copy of your workbooks first!

'To answer your other question, the code I posted earlier,
'found, opened the files and closed them. It did not do
'anything with the data in the files.

'Please note the project reference required!

'--------------------------
Sub ChangeDataInFiles()
'Jim Cone - San Francisco, USA - Jun 09, 2005
'*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
'Opens/closes files in the specified folder that have a numeric suffix
'that falls within a specified range.
'Copies the used range on the first sheet in each file and
'pastes it below all other data on a new sheet added to the 'other' file.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim OutPutRow As Long
Dim lngCount As Long
Dim lngNum As Long
Dim strPath As String
Dim OutSheet As Excel.Worksheet

'Specifies the base file name.
Const strName As String = "quote sheet test"

'Adds a new sheet to receive the data and names it.
With Workbooks("results of running macro.xls")
.Worksheets.Add(Count:=1, after:=.Worksheets(.Worksheets.Count)).Name = _
"New Output " & Format$(Date, "mmddyy")
Set OutSheet = .Worksheets(.Worksheets.Count)
End With
OutPutRow = 1

'Specify the folder...
strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"

'Establish object references
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

'first numeric suffix to look for
lngCount = 1

For Each objFile In objFolder.Files
lngNum = lngCount

Do
'Find the file and open it.
If objFile.Name = strName & lngNum & ".xls" Then
Workbooks.Open strPath & objFile.Name

'*Code to do stuff has been added here*
'Copies the used range and pastes it on the added sheet,
'below any existing data.
Workbooks(objFile.Name).Worksheets(1).UsedRange.Copy _
Destination:=OutSheet.Cells(OutPutRow, 1)
OutPutRow = OutSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

'Close the file without saving it.
Workbooks(objFile.Name).Close savechanges:=False
lngCount = lngCount + 1
Exit Do
Else
lngNum = lngNum + 1
End If
Loop While lngNum < 4 'One more than the number of files

If lngCount > 3 Then Exit For 'The number of files to open

Next 'objFile

Set OutSheet = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'------------------------------


Jim,
Thank you very much for your patience as I am a new to VBA.
I have a couple of questions.

Where should I put this code that you wrote? Prior to each file opening?
"but you have to add the code to massage the files while they are open." I
am not quite sure what you mean here. Can you please elaborate.
Thank You,
Judd