investinthebest
New Member
- Joined
- Jun 7, 2006
- Messages
- 18
This is what I'm trying to accomplish:
open latest file in a folder, copy the contents to a "master file" on a new tab, close the latest file, then find another latest file from a different folder, wash and repeat the above about 10 more times.
This will take a bunch of separate spreadsheets in different locations and put them all into one nice "master file" for me. I found some code that works great for one file, but I can't get the 2nd (and so on) files to work. The macro just pulls in the first file and stops, no errors, but doesn't give me additional tabs either. Below is the code I'm using - one can see I just copied and pasted the code a second time for "variable 2" but changed the variable names and location of the file. Can anyone please help me?
Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
'Specify the path to the folder
MyPath = "C:\Users\abc\Desktop\Excel Test2\"
'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xlsx", vbNormal)
'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
'Loop through each Excel file in the folder
Do While Len(MyFile) > 0
'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)
'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
'Get the next Excel file from the folder
MyFile = Dir
Loop
'Open the latest file
Workbooks.Open MyPath & LatestFile
'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)
'Close the file
ActiveWorkbook.Close False
'Variable 2
'Declare the variables
Dim MyPath1 As String
Dim MyFile1 As String
Dim LatestFile1 As String
Dim LatestDate1 As Date
Dim LMD1 As Date
'Specify the path to the folder
MyPath1 = "C:\Users\abc\Desktop\Excel Test\"
'Make sure that the path ends in a backslash
If Right(MyPath1, 1) <> "\" Then MyPath1 = MyPath1 & "\"
'Get the first Excel file from the folder
MyFile1 = Dir(MyPath1 & "*.xlsx", vbNormal)
'If no files were found, exit the sub
If Len(MyFile1) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
'Loop through each Excel file in the folder
Do While Len(MyFile1) > 0
'Assign the date/time of the current file to a variable
LMD1 = FileDateTime(MyPath1 & MyFile1)
'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD1 > LatestDate1 Then
LatestFile1 = MyFile1
LatestDate1 = LMD1
End If
'Get the next Excel file from the folder
MyFile1 = Dir
Loop
'Open the latest file
Workbooks.Open MyPath1 & LatestFile1
'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)
'Close the file
ActiveWorkbook.Close False
End Sub
open latest file in a folder, copy the contents to a "master file" on a new tab, close the latest file, then find another latest file from a different folder, wash and repeat the above about 10 more times.
This will take a bunch of separate spreadsheets in different locations and put them all into one nice "master file" for me. I found some code that works great for one file, but I can't get the 2nd (and so on) files to work. The macro just pulls in the first file and stops, no errors, but doesn't give me additional tabs either. Below is the code I'm using - one can see I just copied and pasted the code a second time for "variable 2" but changed the variable names and location of the file. Can anyone please help me?
Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
'Specify the path to the folder
MyPath = "C:\Users\abc\Desktop\Excel Test2\"
'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xlsx", vbNormal)
'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
'Loop through each Excel file in the folder
Do While Len(MyFile) > 0
'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)
'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
'Get the next Excel file from the folder
MyFile = Dir
Loop
'Open the latest file
Workbooks.Open MyPath & LatestFile
'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)
'Close the file
ActiveWorkbook.Close False
'Variable 2
'Declare the variables
Dim MyPath1 As String
Dim MyFile1 As String
Dim LatestFile1 As String
Dim LatestDate1 As Date
Dim LMD1 As Date
'Specify the path to the folder
MyPath1 = "C:\Users\abc\Desktop\Excel Test\"
'Make sure that the path ends in a backslash
If Right(MyPath1, 1) <> "\" Then MyPath1 = MyPath1 & "\"
'Get the first Excel file from the folder
MyFile1 = Dir(MyPath1 & "*.xlsx", vbNormal)
'If no files were found, exit the sub
If Len(MyFile1) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
'Loop through each Excel file in the folder
Do While Len(MyFile1) > 0
'Assign the date/time of the current file to a variable
LMD1 = FileDateTime(MyPath1 & MyFile1)
'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD1 > LatestDate1 Then
LatestFile1 = MyFile1
LatestDate1 = LMD1
End If
'Get the next Excel file from the folder
MyFile1 = Dir
Loop
'Open the latest file
Workbooks.Open MyPath1 & LatestFile1
'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)
'Close the file
ActiveWorkbook.Close False
End Sub