Inserting Date Last Modified in lots of excel files without opening

[Ceag]

I keep receiving the same error message:Compile error: wrong number of arguments or invalid property assignment. Word FileDateTime is highlighted.


Sub InsertDateLastModified()
Dim FolderPath As String
Dim Filename As String
Dim wb As Workbook
Dim DateLastModified As Date
Dim strDateLastModified As String
Dim varDateLastModified As Variant


FolderPath = "C:\Users\user\Desktop\Test"
Filename = Dir(FolderPath & "*.xlsx")


Do While Filename <> ""
Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
DateLastModified = FileDateTime(FolderPath & Filename, vbFileLastWriteDateTime)
strDateLastModified = Format(DateLastModified, "dd-mm-yyyy")
varDateLastModified = CVar(strDateLastModified)
strDateLastModified = ToString(varDateLastModified)
wb.Sheets("Sheet2").Range("A50").Value2 = strDateLastModified
wb.Close SaveChanges:=False
Filename = Dir()
Loop

End Sub

Can someone please help me on this?

[Paul_Hossler]

Added CODE tags to format the macro, you can use the [#] icon and paste the macro between

Two changes - don't know if it'll work, but at least it complies



Option Explicit


Sub InsertDateLastModified()
Dim FolderPath As String
Dim Filename As String
Dim wb As Workbook
Dim DateLastModified As Date
Dim strDateLastModified As String
Dim varDateLastModified As Variant




FolderPath = "C:\Users\user\Desktop\Test"
Filename = Dir(FolderPath & "*.xlsx")




Do While Filename <> ""
Set wb = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)
DateLastModified = FileDateTime(FolderPath & Filename) ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
strDateLastModified = Format(DateLastModified, "dd-mm-yyyy")
varDateLastModified = CVar(strDateLastModified)
strDateLastModified = CStr(varDateLastModified) ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
wb.Sheets("Sheet2").Range("A50").Value2 = strDateLastModified
wb.Close SaveChanges:=False
Filename = Dir()
Loop


End Sub




https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filedatetime-function?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1008921)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue



Example


This example uses the FileDateTime function to determine the date and time a file was created or last modified. The format of the date and time displayed is based on the locale settings of your system.
VBCopy
Dim MyStamp
' Assume TESTFILE was last modified on February 12, 1993 at 4:35:47 PM.
' Assume English/U.S. locale settings.
MyStamp = FileDateTime("TESTFILE") ' Returns "2/12/93 4:35:47 PM".

[rollis13]

Hi to all :hi:.
Also, this line probably needs a trailing backslash like this:
FolderPath = "C:\Users\user\Desktop\Test\"

[Ceag]

Correct. Txs!

[Ceag]

Tested. It runs and does not report errors, but cell A50 remains empty.

[rollis13]

Here you are saying: insert "strDateLastModified" in cell A50 of Sheet2 of the workbook (file) you have opened while looping,
wb.Sheets("Sheet2").Range("A50").Value2 = strDateLastModifiedthen, here you say close that file without saving.
wb.Close SaveChanges:=FalseThat's why you will never find updated A50 cells in the files you looped.

[Aussiebear]

Any file that 30 years old needs deletion rather than updating.... :devil2:

[Ceag]

I have hundreds of Excel files which must be (automatically) renamed in a code containing six letters (surname SUR, name NAM), ddmmyy of birth, then a dot, and ddmmyy of Date Last Modified.
I have Name in AG2 cell, from which we must take three letters from second word and three letters from the first one. Than, we must take date of birth from AI2 in ddmmyy format, than, we must insert Date Last Modified in cell A100 in format ddmmyy, and finally command save under new name. Example: John Smith, born 14 April 1969, File last modified 18 October 2020, will end-up as SMIJOH140469.181020.
Can you help?

[Paul_Hossler]

Can you help?

Sure, but it'd be alot easier if you attached a small, sanitized workbook(s) with examples of the before and after

Also, is the current file name inportant or just the worksheet cell data?

Is there more that one sheet, and if so, how to tell which is the right one?


So in workbook .....Something.xlsx

Last modified = 18 Oct 2020

On worksheet ...Something

AG2 = "John Smith'
AI2 = 140469

So rename Something.xlsx to SMIJOH140469.1810.20.xlsx

Is that it?

[Ceag]

Sure, but it'd be alot easier if you attached a small, sanitized workbook(s) with examples of the before and after

Also, is the current file name inportant or just the worksheet cell data?

Is there more that one sheet, and if so, how to tell which is the right one?


So in workbook .....Something.xlsx

Last modified = 18 Oct 2020

On worksheet ...Something

AG2 = "John Smith'
AI2 = 140469

So rename Something.xlsx to SMIJOH140469.1810.20.xlsx

Is that it?

Txs. Note: Rename code should not contain two dots, just one between two ddmmyy.
Workbook path is C:\Users\User\Desktop\2020\
Workbook name is John Smith.xlsx
Sheet: Sheet2
In cell AG2 is name: John Smith
In cell AI2 is Date of Birth: 14.04.1969.
It has Date Last Modified (NOT DATE CREATED!)
Output: workbook is saved in C:\Users\User\Desktop\2020\ as SMIJOH140469.181020.xlsx

[Paul_Hossler]

Txs. Note: Rename code should not contain two dots, just one between two ddmmyy.

Keyboard made a mistake :(

Workbook path is C:\Users\User\Desktop\2020\

OK

Workbook name is John Smith.xlsx

I assume that there's lots of workbooks in that path with names like 'Tom Jones.xlsx' and 'Bill Brown.xlsx' and 'Mary Green.xlsx'

Sheet: Sheet2

ALWAYS Sheet2

In cell AG2 is name: John Smith, In cell AI2 is Date of Birth: 14.04.1969., It has Date Last Modified (NOT DATE CREATED!)

What is the date format? 14.04.1969 or 14041969 (as in your post #8)

Output: workbook is saved in C:\Users\User\Desktop\2020\ as SMIJOH140469.181020.xlsx

[Ceag]

Txs. Note: Rename code should not contain two dots, just one between two ddmmyy.

Keyboard made a mistake :(

Workbook path is C:\Users\User\Desktop\2020\

OK

Workbook name is John Smith.xlsx

I assume that there's lots of workbooks in that path with names like 'Tom Jones.xlsx' and 'Bill Brown.xlsx' and 'Mary Green.xlsx'

Sheet: Sheet2

ALWAYS Sheet2

In cell AG2 is name: John Smith, In cell AI2 is Date of Birth: 14.04.1969., It has Date Last Modified (NOT DATE CREATED!)

What is the date format? 14.04.1969 or 14041969 (as in your post #8)

Output: workbook is saved in C:\Users\User\Desktop\2020\ as SMIJOH140469.181020.xlsx




Txs.
Format is ddmmyy, not ddmmyyyy.

[Paul_Hossler]

Inserting Date Last Modified in lots of excel files without opening

Also, your first macro inserts the LMD into A50, but then doesn't save the file

Why do you want to insert the LMD if you're not saving the file? If you save it, then you get an updated LMD

[Paul_Hossler]

I had to create some test data som they all have the same Modified Date




Option Explicit


Const sPath As String = "C:\Users\Daddy\Desktop\Test\" ' <<<<<<<<<<<<<<<<<<<<< Change


Sub RenameFiles()
Dim oFSO As Object, oFolder As Object, oFiles As Object, oFile As Object
Dim sName As String, sBirth As String, sMod As String
Dim wb As Workbook
Dim vName As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.Getfolder(sPath)

For Each oFile In oFolder.Files
If oFSO.GetExtensionName(oFile.Name) <> "xlsx" Then GoTo GetNext

sMod = Format(oFile.DateLastModified, "ddmmyy")

Set wb = Workbooks.Open(sPath & oFile.Name)

sName = UCase(wb.Worksheets("Sheet2").Range("AG2"))
vName = Split(sName, " ")
sName = Left(vName(1), 3) & Left(vName(0), 3)

sBirth = Format(wb.Worksheets("Sheet2").Range("AI2").Value, "ddmmyy")

wb.Close False

Call oFSO.MoveFile(sPath & oFile.Name, sPath & sName & sBirth & "." & sMod & "." & oFSO.GetExtensionName(oFile.Name))


GetNext:
Next


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Paul_Hossler]

And the output files

[Ceag]

And the output files

3095330954

[georgiboy]

Have you checked the file path, did you swap the word 'user' for your name?

[Aussiebear]

Have you checked the file path, did you swap the word 'user' for your name?
There's a trap for new users...

[Ceag]

Of course...

Have you checked the file path, did you swap the word 'user' for your name?

[georgiboy]

I asked as it worked form me when I downloaded the files and placed them in a folder.

Have you kept the \ on the end of the path?

I would say that the path being wrong seems to be the issue. Make sure 100% that the path leads to the folder in question.

You can copy the path from the code and paste it into the file explorer and see if it takes you to the folder or not.

[Ceag]

I asked as it worked form me when I downloaded the files and placed them in a folder.

Have you kept the \ on the end of the path?

I would say that the path being wrong seems to be the issue. Make sure 100% that the path leads to the folder in question.

You can copy the path from the code and paste it into the file explorer and see if it takes you to the folder or not.


Code is superb. Path is ok, it seems that code stops when finds a file that does not have appropriate value in target cells.

[Ceag]

Code is working properly providing files are correct (appropriate values in targeting cells). When a file with wrong values is open, it stops.
Thanks, thanks, thanks!


I had to create some test data som they all have the same Modified Date




Option Explicit


Const sPath As String = "C:\Users\Daddy\Desktop\Test\" ' <<<<<<<<<<<<<<<<<<<<< Change


Sub RenameFiles()
Dim oFSO As Object, oFolder As Object, oFiles As Object, oFile As Object
Dim sName As String, sBirth As String, sMod As String
Dim wb As Workbook
Dim vName As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.Getfolder(sPath)

For Each oFile In oFolder.Files
If oFSO.GetExtensionName(oFile.Name) <> "xlsx" Then GoTo GetNext

sMod = Format(oFile.DateLastModified, "ddmmyy")

Set wb = Workbooks.Open(sPath & oFile.Name)

sName = UCase(wb.Worksheets("Sheet2").Range("AG2"))
vName = Split(sName, " ")
sName = Left(vName(1), 3) & Left(vName(0), 3)

sBirth = Format(wb.Worksheets("Sheet2").Range("AI2").Value, "ddmmyy")

wb.Close False

Call oFSO.MoveFile(sPath & oFile.Name, sPath & sName & sBirth & "." & sMod & "." & oFSO.GetExtensionName(oFile.Name))


GetNext:
Next


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Ceag]

Paul_Hossler is king of VB.

[Paul_Hossler]

<blush>

Without seeing how the data could go wrong, I could only guess at some of the likely failures to add a check



Option Explicit


Const sPath As String = "C:\Users\Daddy\Desktop\Test\" ' <<<<<<<<<<<<<<<<<<<<< Change


Sub RenameFiles()
Dim oFSO As Object, oFolder As Object, oFiles As Object, oFile As Object
Dim sName As String, sBirth As String, sMod As String
Dim wb As Workbook
Dim vName As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.Getfolder(sPath)

For Each oFile In oFolder.Files
If oFSO.GetExtensionName(oFile.Name) <> "xlsx" Then GoTo GetNext
If oFile.DateLastModified = 0 Then GoTo GetNext
If Not IsDate(oFile.DateLastModified) Then GoTo GetNext
sMod = Format(oFile.DateLastModified, "ddmmyy")

Set wb = Workbooks.Open(sPath & oFile.Name)

sName = UCase(wb.Worksheets("Sheet2").Range("AG2"))
If Len(sName) = 0 Then GoTo GetNext
vName = Split(sName, " ")
If UBound(vName) <> 1 Then GoTo GetNext
sName = Left(vName(1), 3) & Left(vName(0), 3)

If Len(wb.Worksheets("Sheet2").Range("AI2").Value) = 0 Then GoTo GetNext
If Not IsDate(wb.Worksheets("Sheet2").Range("AI2").Value) Then GoTo GetNext
sBirth = Format(wb.Worksheets("Sheet2").Range("AI2").Value, "ddmmyy")

wb.Close False

Call oFSO.MoveFile(sPath & oFile.Name, sPath & sName & sBirth & "." & sMod & "." & oFSO.GetExtensionName(oFile.Name))


GetNext:
Next


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Ceag]

One more thing. I need an advice. Now I have thousands of files with names consisted of six capital letters, six numbers, dot, six numbers, dot, xlsx (I.E. SMIJOH140469.040823). What I would like to do is to have all of them sorted in subfolders made according to first twelve symbols (I.E. SIMJOH140469), AND I would like ALL the files starting with same twelve symbols to end-up in the same subfolder. (I.E. SMIJOH140469.040823, SMIJOH140469.080223, SMIJOH140469.030922 to folder SMIJOH140469.
Can it be done?