To check if excel files are updated today, then run a macro!

[Longbow9]

A question of "to run, or not to run!"?!

I have a database which I've 'helped' in creating but now want to make others available to update - in the past this has not gone well! The database uses data from two excel sheets and the problem has been people do not always check if these have been updated today before running and therefore the database gets loaded with old data.

I have found code that will check and display a message showing datelastmodified for a file but what i'm trying to find is code which will check both files' datelastmodified, if they are modified today then run the update macro, if one or both are not modified today, then to display a message saying that the excel is not updated.

I am a complete novice and landed the job of looking after this as everyone else was 'too busy' so really really appreciate advice and assistance!

Thanks

William

 

[GohDiamond]

Here's the concept

Dim File1, File2, dtFile1, dtFile2

File1 = "Your Excel File"
File2 = "Your Other Excel File"

dtFile1 = DateLastmodified (File1)
dtFile2 = DateLastmodified (File2)​

If dtFile1 < Date() or dtFile2 < Date() then

msgbox "Your Message Here"
Exit​

else

'Continue Processing​

End If​

Hands on Experience can be the best teacher. Add this concept to your SUB or FUNCTION. It'll take a little customizing. if you get stuck, post your actual code and we'll get more specific.

Cheers!
Goh

 

[Longbow9]

Hi Goh,

Thank you for your message.

I've added the code but it comes up saying:
"Compile Error:
Sub or function not defined"

I also noted the () brackets do not stay in the code when i enter it!

 

[GohDiamond]

I have found code that will check and display a message showing datelastmodified for a file

Can you Post the code you plan to use to get the datelastmodified?

Goh

 

[Longbow9]

Hi Goh,

Dim oFS As Object
Dim strFilename As String

'Put your filename here
strFilename = "c:\excel stuff\commandbar info.xls"


'This creates an instance of the MS Scripting Runtime FileSystemObject class
Set oFS = CreateObject("Scripting.FileSystemObject")

MsgBox strFilename & " was created on " & oFS.GetFile(strFilename).DateCreated



Set oFS = Nothing

End Sub

Will show the date in a message box but i have no idea how to modify it to work as i wish with checking and running a macro, or stopping if date is old.

 

[vbaInet]

By simply opening a file, changing an 'a' to a 'b', changing the 'b' back to an 'a', and then closing and saving the file, will alter the Date Modified property of the file.

If changes to the file are explicitly new added records (with unique IDs) then you don't even need this validation routine.

If changes to the file are a mixture of both newly added records and changes to existing records then you'll need an SHA1 or an MD5 checksum. Either methods will truly indicate whether a file has been changed. Scour the net for both examples.

 

[Longbow9]

The reason i need this to run is i currently have scripts which run and export excel files to a folder. I just need to see 2 files in the folder were created today, if they have been created today i can allow my database to update, if not i want it to display a message so people can manually update it.

Still no luck finding something which works!

 

[vbaInet]

Just need to clarify, because the objective has changed, do you now want to check the Date Created property instead of the Date Modified property? If all you want to check for is newly created files then you can do that via FileSystemObject. If it's the latter, then my suggestion in my last post still holds.

Date Created - FileSystemObject below:
https://msdn.microsoft.com/en-us/library/office/gg264090.aspx