Home How to loop through a folder of workbooks, copy and paste
Reply: 0

How to loop through a folder of workbooks, copy and paste

ABC
1#
ABC Published in 2017-12-07 17:16:24Z

Please advise how to loop through a folder for the latest workbooks as there's always an update file, copy and paste the file data?

Hey guys, sorry for missing the code in the earlier question:

Force the explicit delcaration of variables Option Explicit Sub OpenLatestFile()

'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 = "\\SNG-FS-01\PacificLight Power (City)\Working Files\Consolidate File\2017\"

'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 & "*.xls", 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     

End Sub

With this code able to open the latest file, but have no idea how to copy the open file data (file name is subject to change) and paste it into the MyFile

Appreciate if anyone of you could help on this

Vba
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298674 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO