vba import *.xls with todays date

[Pinaceous]

Hi All,

I'm importing a xls file into an xlsm file.

This xls file is created each day onto a workers desk top.

My question is, is there a sub that I can provide to first check if the xls file has todays date in the 'Date modified' string before my other sub begins the import?

Does anyone have a sub that can check if the importing file has todays date?

Please let me know if you do.

Thank you!
pinaceous

 

[Pinaceous]

Just to follow up post here, with this type of problem


I'm can use this sub to check the 'Date modified' string:

Sub test()
    Const strFullName As String = "C:\Users\Desktop\Test.xlsx"

    
    If FileExists(strFullName) Then
        MsgBox FileLastModified(strFullName)
    Else
        MsgBox "Cannot find the file : " & vbNewLine & strFullName
    End If
    
End Sub
 
Private Function FileExists(fName) As Boolean
    Dim x As String
    x = Dir(fName)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function
 
Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String
     
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)
     
    s = UCase(strFullFileName) & vbCrLf
    s = s & "Last Modified: " & f.DateLastModified
    FileLastModified = s
     
    Set fs = Nothing: Set f = Nothing
     
End Function

But how do I modify/add to this sub if todays date

 FileLastModified(strFullName)

Then Msgbox "Todays Date"

Thanks,
Paul

 

[Pinaceous]

Bump!

 

[John_w]

The whole thing can be just:

Sub test()
    
    Const strFullName As String = "C:\Users\Desktop\Test.xlsx"
    
    strFullName = ThisWorkbook.FullName
    
    If Dir(strFullName) <> vbNullString Then
        If Int(FileDateTime(strFullName)) = Date Then
            MsgBox strFullName & " was modified today"
        End If
    Else
        MsgBox strFullName & " doesn't exist"
    End If
    
End Sub

 

[Pinaceous]

The whole thing can be just:

Sub test()
    
    Const strFullName As String = "C:\Users\Desktop\Test.xlsx"
    
    strFullName = ThisWorkbook.FullName
    
    If Dir(strFullName) <> vbNullString Then
        If Int(FileDateTime(strFullName)) = Date Then
            MsgBox strFullName & " was modified today"
        End If
    Else
        MsgBox strFullName & " doesn't exist"
    End If
    
End Sub

Thanks John_w!

Can this code be applied without the

Const strFullName As String = "C:\Users\Desktop\Test.xlsx"

??


Many thanks,
Paul

 

[John_w]

Thanks John_w!

Can this code be applied without the

Const strFullName As String = "C:\Users\Desktop\Test.xlsx"

??


Many thanks,
Paul

The file name has to be specified or supplied somewhere. (NB I left the ThisWorkbook line in whilst testing and it should be deleted.)

I'm not sure what you're asking for as the code you posted is different to the thread title. If you want multiple .xls files, use a Dir function loop with the FileDateTime check inside it. There should be example code on the forum.

 

[Pinaceous]

Hi John_w,

We are good. I'm not looking for a Dir function low with the FileDateTime check inside it but I do have a question about your supplied code.

How would I create/add to the situation whereby if the file exist but does not equal today's date situation.


For example like ...

If Int(FileDateTime(strFullName)) =/= Date Then
            MsgBox strFullName & " WAS NOT modified today"
        End If

How would I supply this condition into your code?

Many thanks,
Paul

 

[Pinaceous]

Oh wait, John_w I got it:

If Not Int(FileDateTime(strFullName)) = Date Then
            
            MsgBox strFullName & " was not modified today"
            
        End If

Sorry about that.

But actually above in Post#6, when I asked about the

Const strFullName As String

, is there a way for the sub to use something like:

strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

Whereby, it will search where the file already exist?

Thanks,
Paul

PS Provided that the File Name is constant?

 

[John_w]

I'm not sure what your last code is meant to show.

Maybe this - the code looks for the file name "Test.xlsx" in the same folder as the macro workbook.

Public Sub test2()
    
    Dim strFullName As String
    
    strFullName = ThisWorkbook.Path & "\Test.xlsx"
    
    If Dir(strFullName) <> vbNullString Then
        If Int(FileDateTime(strFullName)) = Date Then
            MsgBox strFullName & " was modified today"
        Else
            MsgBox strFullName & " was not modified today"
        End If
    Else
        MsgBox strFullName & " doesn't exist"
    End If
    
End Sub