Open Newest Files (from Multiple Folders) and Save Them in Another Folder | Excel VBA Macro
HTML-код
- Опубликовано: 15 окт 2024
- Excel VBA Macro: Open Newest Files (from Multiple Folders) and Save Them in Another Folder. In this video, we create code that checks a list of folders, pulls the latest file out of each one, and saves them all to another specified folder.
Code (RUclips doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub save_newest_files_to_folder()
Dim myPath As String
Dim myFile As String
Dim destination As String
Dim newestFile As String
Dim newestDate As Date
Dim fldr_count As Integer
Dim ws As Worksheet
Dim i As Integer
Dim check As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ThisWorkbook.Sheets("Sheet1")
fldr_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
destination = ws.Cells(2, 2).Text & "\"
For i = 2 To fldr_count
check = 0
myPath = ws.Cells(i, 1).Text & "\"
myFile = Dir(myPath)
newestFile = myFile
On Error GoTo noFiles
newestDate = FileDateTime(myPath & myFile)
Do While myFile LT GT ""
check = 1
If FileDateTime(myPath & myFile) GT newestDate Then
newestFile = myFile
newestDate = FileDateTime(myPath & myFile)
End If
myFile = Dir
Loop
Workbooks.Open Filename:=myPath & newestFile
ActiveWorkbook.SaveAs Filename:=destination & newestFile
ActiveWorkbook.Close
noFiles:
If check = 0 Then
MsgBox "There are no files in this folder: " & myPath
End If
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
#ExcelVBA #ExcelMacro
Appreciate your work. I actually learned a lot from you. Your videos are so different than other RUclipsr. I wish you can make more videos later soon.
Thank you so much!! I’ll start making them again soon
Will you please help modify your VBA code to find files from a list ( column 1, those will be paths to multiple folders etc) and save all in one destination folder. Thank you
And if you have any ideas on macro for creating reoccurring action plan list for tasks that occur monthly
Please make a video for VBA to Save a specific Excel sheet using data from cells & single click
Help me to make a VBA code to send 20 files. Just like you did it need to add up a little bit more. Let me give details a little bit. Each mail has a different attachment and mail body. Like if I am sending my 1st mail. I will open that excel file, go to a particular sheet, copy data, paste it to the email body, and attach that same file as an attachment. Once the mail is ready then that file gets closed. And then with 2nd mail and it will go on for 19 times.
Hi,
Can you please do some videos on the following
Creating new worksheet for each day of the month automatically only show that sheet for specific day and have a workbook for each month with all the data
Copying specific columns from different workbooks to one workbook sheet
Will do! Thanks!!