none
VBA coding for update file based on the most recent date in a folder RRS feed

  • Question

  • Hello my name is Rio

    I have one workbook (excel workbook) named sales dashboard

    And there is a button named "Update Data" to update data for the most recent data in a folder (09. Database SRC)

    I wanna ask how to add the most recent data from folder 09. Database SRC use VBA Macro?

    So when I click button "update data", the data from the most recent data in folder 09. Database SRC will be copied automatically to my excel (sales dashboard)?

    Please kindly help me

    Thank you

    Tuesday, February 14, 2017 7:36 AM

All replies

  • Do you have multiple files in the folder, with data by date, or do you have a file with date stamps as one field in your data set?
    Tuesday, February 14, 2017 2:12 PM
  • I have multiple files in the folder, with data by date

    Every Monday, the newest data will be added again

    Thank you

    Thursday, February 16, 2017 1:28 AM
  • You can use code like this to open the newest file in a known folder (if they are all Excel files), and then use the opened file to get the data of interest to your dashboard.

    Sub GetNewestFile()
        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.Folder
        Dim colFiles As Scripting.Files
        Dim objfile As Scripting.File
        Dim objNewest As Scripting.File
        Dim dteFileDate As Date
        Dim strPath As String
        Dim wkbkNewest As Workbook
        
        'Change the path
        strPath = "C:\Test"
        
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(strPath)
        Set colFiles = objFolder.Files
        For Each objfile In colFiles
            If dteFileDate < objfile.DateLastModified Then
                dteFileDate = objfile.DateLastModified
                Set objNewest = objfile
            End If
        Next
         
        MsgBox "The newest file is """ & strPath & "\" & objNewest.Name & """, last changed on " & Format(dteFileDate, "mmm dd, yyyy")
        
        Set wkbkNewest = Workbooks.Open(strPath & "\" & objNewest.Name)
    End Sub

    Thursday, February 16, 2017 3:08 PM