Extract file based upon Creation Date and Size

[Max Alejandro Molina]

Hello Excel Ninjas,

Everyonce I week a Macro to extract the info from a text file, format it and paste into my excel workbook, but sometimes the text file is not updated or the file is being exported and it weighs 0kb at the moment, I wanna know if I can prove that the .txt file weighs more than 0kb and then If wheter the .txt. file is from today().

*If the macro detects the .txt file weighs 0kb or is <> from today() then i want a message box that says "Keep in mind that this info will be missing", and then it continues with the other 2 .txt files..

Attached you will find the Macro I use..

Thank you all!

 

[ThauThème]

Hi Alejandro,

Maybe the code below will help you :

Dim P As String 'declare the variable P (Path)
Dim SF As Object 'declare the variable SF (System Files)
Dim FL As Object 'declare the variable FL (FoLder)
Dim FS As Object 'declare the variable FS (FileS)
Dim F As Object 'declare the variable F (File)

P = "C:\Users\...\Documents" 'define the path P (to adapt...)
Set SF = CreateObject("Scripting.FileSystemobject") 'define the variable SF
Set FL = SF.GetFolder(P) 'define the folder FL
Set FS = FL.Files 'define the ensemble of files from FL
For Each F In FS 'loop for each file F in the emsemble of files FS
    If UCase(Right(F.Name, 3)) = "TXT" Then 'condition 1 : if files is TXT type
        If F.Size > 0 And Split(F.DateCreated, " ")(0) = CStr(Date) Then 'condition 2 : If size file > 0 and creation date is same that date
            'your code...
        End If 'end of condition 2
    End If 'end of condition 1
Next F 'next file from loop

 

[Max Alejandro Molina]

Hi Thau,

I actually have 3 differents folders from where get the info from and also in each folder there are more than 50 .txt files, other thing tp consider is that the .txt name never changes they will only be updated once a week (the date I happen to create the report) keeping the same date, so is there any way to just enter the link inlcuding the file name.txt??

 

[ThauThème]

Hi Alejadro,

This new code if entire file name (with path) is in the column A. To be adapted :

Sub Macro1()
Dim SF As Object
Dim O As Worksheet
Dim FL As Object
Dim FS As Object
Dim F As Object

Set SF = CreateObject("Scripting.FileSystemobject")
Set O = Sheets("Sheet1")
For Each CEL In O.Range("A1:A" & Cells(Application.Rows.Count, 1).End(xlUp).Row)
    NS = UBound(Split(CEL.Value, "\"))
    FN = Split(CEL.Value, "\")(NS)
    P = Left(CEL.Value, Len(CEL.Value) - (Len(FN) + 1))
    Set FL = SF.GetFolder(P)
    Set FS = FL.Files
    For Each F In FS
        If F.Name = FN Then
            If F.Size > 0 And Split(F.DateCreated, " ")(0) = CStr(Date) Then
                MsgBox FN 'your code here...
                Exit For
            End If
        End If
    Next F
Next CEL
End Sub