asked on

Need Excel macro to open all files in specific folder that have a Created Date of the current system date

I need an Excel macro that looks in a specific folder, and then opens any/all Excel files that were created on whatever the current date is.

Thanks!!!

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BEBaldauf

ASKER

Works great, thank you!!!!

Glenn Ray

Hi, the following subroutine - along with a userform that will let you browse and locate a specific folder - will open all the Excel files (*.xls*) in that folder whose creation date is the current (today's) date.

Sub Open_Current_Files()
    Dim fso, fld As Object
    Dim strPath, strFile As String
    Dim wbk As Workbook
    Dim dtCreationDate As Date
    Application.ScreenUpdating = False

    frmGetFileXLS.Show
    If Not (boolGotFile) Then Exit Sub
    strPath = frmGetFileXLS.txtPath

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strPath)

    strFile = Dir(strPath & "\*.xls*")
    
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strPath & "\" & strFile, UpdateLinks:=0, AddToMRU:=False)
        dtCreationDate = wbk.BuiltinDocumentProperties("Creation Date")
        If Round(dtCreationDate, 0) <> Round(Now(), 0) Then
            wbk.Close savechanges:=False
        End If
        strFile = Dir
    Loop
 End Sub

Open in new window

I've attached an example file that will run this; just click the macro button, browse to the folder, click any Excel file, then click OK to begin. It will open each file in the folder, but close those with creation dates earlier than the current date, leaving the rest open.

Regards,
-Glenn
EE-Q-28487107.xlsm

Glenn Ray

Ack...slow on the draw. Should have refreshed before posting. Cool to see another method applied, however.

-Glenn