Copy most recent file from directory & paste to current worksheet

Pumper

Board Regular
Joined
Sep 12, 2013
Messages
114
Office Version
  1. 365
Hi All,

I am trying to copy the most recent data (Sheet1) in a directory, the file will always start with "SecuritiesBOD" but can have various endings.

There will be multiple files in the directory starting with "SecuritiesBOD"

Here is the code I currently have but is doesn't bring back the most recent file by date modified.

Any assistance on how to solve this would be greatly appreciated!

Sub CallFile()

Application.ScreenUpdating = False

Const FILEPATH As String = "G:\Datafiles\"
Const FILESPEC As String = "SecuritiesBOD*.csv"

Dim wbMyFile As Workbook
Dim wbxlFile As Workbook
Dim shData As Worksheet
Dim xlFile As String

Set wbMyFile = ThisWorkbook
Set shData = wbMyFile.Worksheets("Sheet1")

xlFile = Dir(FILEPATH & FILESPEC)
Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)

shData.Range("A1:Z2000").Clear
wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
shData.Range("A1")

Debug.Print xlFile, wbxlFile.Name
Debug.Print wbMyFile.Name

wbxlFile.Close SaveChanges:=False

Range("A1").Select

Application.ScreenUpdating = True

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi All,

I am trying to copy the most recent data (Sheet1) in a directory, the file will always start with "SecuritiesBOD" but can have various endings.

There will be multiple files in the directory starting with "SecuritiesBOD"

Here is the code I currently have but is doesn't bring back the most recent file by date modified.

Any assistance on how to solve this would be greatly appreciated!

Sub CallFile()

Application.ScreenUpdating = False

Const FILEPATH As String = "G:\Datafiles\"
Const FILESPEC As String = "SecuritiesBOD*.csv"

Dim wbMyFile As Workbook
Dim wbxlFile As Workbook
Dim shData As Worksheet
Dim xlFile As String

Set wbMyFile = ThisWorkbook
Set shData = wbMyFile.Worksheets("Sheet1")

xlFile = Dir(FILEPATH & FILESPEC)
Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)

shData.Range("A1:Z2000").Clear
wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
shData.Range("A1")

Debug.Print xlFile, wbxlFile.Name
Debug.Print wbMyFile.Name

wbxlFile.Close SaveChanges:=False

Range("A1").Select

Application.ScreenUpdating = True

End Sub
This function will return the latest file in the given folder and to the given specification.

For example:

strFile = fncLatestFile("G:\Datafiles\", "SecuritiesBOD*.csv")

will return the latest CSV file in the G:\Datafiles\ folder.

VBA Code:
Public Function fncLatestFile(strFolder As String, strLike As String) As String
Dim strFile As String
Dim strLatestFile As String

    strFile = Dir(strFolder)
    
    Do While strFile <> ""
    
        If strFile Like strLike Then
                
            If strLatestFile = "" Then
                strLatestFile = strFile
            Else
                        
                If FileDateTime(strFolder & strFile) > FileDateTime(strFolder & strLatestFile) Then
                    strLatestFile = strFile
                End If
            
            End If
            
        End If
        
        strFile = Dir
    
    Loop
    
    fncLatestFile = strLatestFile

End Function
 
Upvote 0
This function will return the latest file in the given folder and to the given specification.

For example:

strFile = fncLatestFile("G:\Datafiles\", "SecuritiesBOD*.csv")

will return the latest CSV file in the G:\Datafiles\ folder.

VBA Code:
Public Function fncLatestFile(strFolder As String, strLike As String) As String
Dim strFile As String
Dim strLatestFile As String

    strFile = Dir(strFolder)
   
    Do While strFile <> ""
   
        If strFile Like strLike Then
               
            If strLatestFile = "" Then
                strLatestFile = strFile
            Else
                       
                If FileDateTime(strFolder & strFile) > FileDateTime(strFolder & strLatestFile) Then
                    strLatestFile = strFile
                End If
           
            End If
           
        End If
       
        strFile = Dir
   
    Loop
   
    fncLatestFile = strLatestFile

End Function
Thanks for taking a look Herakles!

I am not entirely sure where I am going wrong as it is still pulling back an older copy, I added your Function below my code and changed the strFile line in your code as you suggested to strFile = fncLatestFile("G:\Datafiles\", "SecuritiesBOD*.csv")

I assume there is something more needing changed in my original code?

Much appreciated on your help for even looking
 
Upvote 0
Thanks for taking a look Herakles!

I am not entirely sure where I am going wrong as it is still pulling back an older copy, I added your Function below my code and changed the strFile line in your code as you suggested to strFile = fncLatestFile("G:\Datafiles\", "SecuritiesBOD*.csv")

I assume there is something more needing changed in my original code?

Much appreciated on your help for even looking
Can you submit your latest code using the VBA code tags.

Use the VBA option on the MrExcel menu.
 
Upvote 0
VBA Code:
Sub Direct_Smalls()

    Application.ScreenUpdating = False
    
    Const FILEPATH As String = "G:\Datafiles\"
    Const FILESPEC As String = "SecuritiesBOD*.csv"

    Dim wbMyFile As Workbook
    Dim wbxlFile As Workbook
    Dim shData As Worksheet
    
    Set wbMyFile = ThisWorkbook
    Set shData = wbMyFile.Worksheets("Sheet1")
    

    Dim xlFile As String
    xlFile = Dir(FILEPATH & FILESPEC)
    Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)
    
    
    shData.Range("A1:Z2000").Clear
    
    wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
    shData.Range("A1")
    
    Debug.Print xlFile, wbxlFile.Name
    Debug.Print wbMyFile.Name
    
    wbxlFile.Close SaveChanges:=False
    Range("A1").Select
    Application.ScreenUpdating = True

End Sub


Public Function fncLatestFile(strFolder As String, strLike As String) As String
Dim strFile As String
Dim strLatestFile As String

    'strFile = Dir(strFolder)
    strFile = fncLatestFile("G:\Datafiles\", "SecuritiesBOD*.csv")
    
    Do While strFile <> ""
    
        If strFile Like strLike Then
                
            If strLatestFile = "" Then
                strLatestFile = strFile
            Else
                        
                If FileDateTime(strFolder & strFile) > FileDateTime(strFolder & strLatestFile) Then
                    strLatestFile = strFile
                End If
            
            End If
            
        End If
        
        strFile = Dir
    
    Loop
    
    fncLatestFile = strLatestFile

End Function
 
Upvote 0
Try this.

You did not need to change the function to get the file name.

You also did not call the function to return the file name from your code.

VBA Code:
Sub Direct_Smalls()

    Application.ScreenUpdating = False
    
    Const FILEPATH As String = "C:\Dump\Copy most recent file from directory & paste to current worksheet\"
    Const FILESPEC As String = "SecuritiesBOD*.csv"

    Dim wbMyFile As Workbook
    Dim wbxlFile As Workbook
    Dim shData As Worksheet
    Dim xlFile As String
    
    Set wbMyFile = ThisWorkbook
    Set shData = wbMyFile.Worksheets("Sheet1")
          
    xlFile = fncLatestFile(FILEPATH, FILESPEC)
        
    Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)
        
    shData.Range("A1:Z2000").Clear
    
    wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
    shData.Range("A1")
    
    Debug.Print xlFile, wbxlFile.Name
    Debug.Print wbMyFile.Name
    
    wbxlFile.Close SaveChanges:=False
    Range("A1").Select
    Application.ScreenUpdating = True

End Sub

Public Function fncLatestFile(strFolder As String, strLike As String) As String
Dim strFile As String
Dim strLatestFile As String

    strFile = Dir(strFolder)
    
    Do While strFile <> ""
    
        If strFile Like strLike Then
                
            If strLatestFile = "" Then
                strLatestFile = strFile
            Else
                        
                If FileDateTime(strFolder & strFile) > FileDateTime(strFolder & strLatestFile) Then
                    strLatestFile = strFile
                End If
            
            End If
            
        End If
        
        strFile = Dir
    
    Loop
    
    fncLatestFile = strLatestFile

End Function
 
Upvote 0
Solution
Try this.

You did not need to change the function to get the file name.

You also did not call the function to return the file name from your code.

VBA Code:
Sub Direct_Smalls()

    Application.ScreenUpdating = False
   
    Const FILEPATH As String = "C:\Dump\Copy most recent file from directory & paste to current worksheet\"
    Const FILESPEC As String = "SecuritiesBOD*.csv"

    Dim wbMyFile As Workbook
    Dim wbxlFile As Workbook
    Dim shData As Worksheet
    Dim xlFile As String
   
    Set wbMyFile = ThisWorkbook
    Set shData = wbMyFile.Worksheets("Sheet1")
         
    xlFile = fncLatestFile(FILEPATH, FILESPEC)
       
    Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)
       
    shData.Range("A1:Z2000").Clear
   
    wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
    shData.Range("A1")
   
    Debug.Print xlFile, wbxlFile.Name
    Debug.Print wbMyFile.Name
   
    wbxlFile.Close SaveChanges:=False
    Range("A1").Select
    Application.ScreenUpdating = True

End Sub

Public Function fncLatestFile(strFolder As String, strLike As String) As String
Dim strFile As String
Dim strLatestFile As String

    strFile = Dir(strFolder)
   
    Do While strFile <> ""
   
        If strFile Like strLike Then
               
            If strLatestFile = "" Then
                strLatestFile = strFile
            Else
                       
                If FileDateTime(strFolder & strFile) > FileDateTime(strFolder & strLatestFile) Then
                    strLatestFile = strFile
                End If
           
            End If
           
        End If
       
        strFile = Dir
   
    Loop
   
    fncLatestFile = strLatestFile

End Function
Thank You! worked perfectly (I just changed to G:\Datafiles\ from " Const FILEPATH As String = "C:\Dump\Copy most recent file from directory & paste to current worksheet\" (I assume this was from you testing)

Very kind of you to take the time to solve this, will be very useful on multiple spreadsheets (y)
 
Upvote 0
Thank You! worked perfectly (I just changed to G:\Datafiles\ from " Const FILEPATH As String = "C:\Dump\Copy most recent file from directory & paste to current worksheet\" (I assume this was from you testing)

Very kind of you to take the time to solve this, will be very useful on multiple spreadsheets (y)
I forgot to change the folder.

I don't have a G drive.

The function can be used again and again and could even be changed to return the earliest Or the latest file.

What the calling code needed would need to be added as another parameter.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top