Copy most recent file from directory & paste to current worksheet

P

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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Pumper said:
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
Click to expand...
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
Herakles said:
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
Click to expand...
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
Pumper said:
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
Click to expand...
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
Herakles said:
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
Click to expand...
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
Pumper said:
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)
Click to expand...
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
You must log in or register to reply here.

Similar threads

B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
730
Joe4
Joe4
M
  • Question Question
How to copy contents of open workbook to current workbook
Replies
1
Views
529
rlv01
R
A
  • Question Question
Arrays Dictionary for loop... Most efficient way to copy files from list1 and paste them on sheet name from list2
Replies
0
Views
673
andnand
A
G
  • Question Question
Open most recent file in folder, unmerge, copy, go to different file, paste. Need code, please help
Replies
1
Views
1K
Greg_ismyusername
G
K
  • Question Question
Help copying a specific named sheet from multiple workbooks onto one sheet in another workbook?
Replies
2
Views
1K
vw412
V

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

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