Solved How to add a date to an Excel file, Part 2

Dell / Inspiron 17r
August 1, 2020 at 06:43:09
Specs: windows 10, 2.3/8
Several weeks ago, Hackoo wrote this batch file to add a date to an Excel file.

If your file is named as Track.xlsx and it's always in your Desktop , you can try with this batch file : Just copy and paste this code below in your notepad or notepad++ and save it as Rename_Date_XLSX.bat and execute by double click !
@echo off
Color 0A & Mode 80,8
Title Get Date and Time and rename file with date
Set "Desktop=%USERPROFILE%\Desktop"
Set "File2Rename=%Desktop%\Track.xlsx"
@for %%a in ("%File2Rename%") do (
set "FileName=%%~na"
set "Ext=%%~xa"
)

echo(
echo( Current Name = "%FileName%%Ext%"
Call :Get_Date_Time
echo( Date : %Year%-%Month%-%Day%
echo( Time : %Hour%:%Min%:%Sec%
Set "NEW_DATE_NAME=%Month%%Day%%Year%"
echo( NEW_DATE_NAME = "%FileName%%NEW_DATE_NAME%%Ext%"
If Exist "%File2Rename%" (
REN "%File2Rename%" "%FileName%%NEW_DATE_NAME%%Ext%"
) Else (
Color 0C
echo( The file "%File2Rename%" does not exist - Please check it!
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------
The batch file is not working correctly. I get the following"

Current Name = "TMBicycle.xlsx"
Date : 2020-07-31
Time : 15:04:37
NEW_DATE_NAME = "TMBicycle07312020.xlsx"
The file "C:\Users\DrWac\Desktop\TMBicycle.xlsx" does not exist - Please check it!

The first time I updated the file its name was "TMBicycle.xlsx" and it updated to "TMBicycle07252020. The second time I tried to update I got the above error message. I don't know a whole lot about batch files but shouldn't the "Current Name" now be TMBicycle07252020.xlsx?

Thank you.
Brian W

message edited by Brian W


See More: How to add a date to an Excel file, Part 2


✔ Best Answer
August 4, 2020 at 11:28:08
@Brian W
Check this last code to rename all "*.xlsx" on your desktop
Just copy and paste in your Notepad as RenameIT_Date.bat and execute it with double click !

@echo off
Color 0A & Mode 80,35
Title Get Date and Time and rename file with date
set "DesktopFolder=%userprofile%\Desktop"
set "Ext=xlsx"
SET /A "Count=0"
CD /D "%DesktopFolder%"
Setlocal EnableDelayedExpansion
@FOR /F "delims=" %%A IN ('dir /B "%DesktopFolder%\*.%Ext%" 2^>nul') DO (
	SET /a "Count+=1"
	SET "FileName[!Count!]=%%~nA"
	SET "Ext=%%~xA"
)

If "%Count%" EQU "0" (
	cls & Color 0C
	echo(
	Echo( There are no files with this extension "%Ext%"
	Timeout /T 3 /NoBreak>nul & Exit
) 

@For /L %%i in (1,1,%Count%) do (
	Call :RunPS "!FileName[%%i]!" NewFileName
	echo(
	echo( Current Name = "!NewFileName!!Ext!"
	Call :Get_Date_Time
	echo( Date : !Year!-!Month!-!Day!
	echo( Time : !Hour!:!Min!:!Sec!
	Set "NEW_DATE_NAME=!Month!!Day!!Year!"
	echo( NEW_DATE_NAME = "!NewFileName!!NEW_DATE_NAME!!Ext!"
	REN "!FileName[%%i]!!Ext!" "!NewFileName!!NEW_DATE_NAME!!Ext!" 2>nul
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------
:Help
Color 0C
echo(
echo      You should drag and drop an EXCEL file over, 
echo      this script "%~nx0" to be renamed !
Timeout /T 10 /NoBreak>nul
Exit
::--------------------------------------------------------------------------------------------
REM This function that use regex in Powershell 
REM In order to help us to extract from string any no digits number
:RunPS <PassPSCMD> <RetValue>
Set psCmd="&{$re=[regex]'[^^\d]+'; $re.Match('%1').value}"
@for /F "usebackq tokens=*" %%i in (`Powershell %psCmd%`) do set "%2=%%i"
Goto:eof
:: End of :RunPS function
::--------------------------------------------------------------------------------------------



#1
August 2, 2020 at 05:51:59
You can try this batch file with drag and drop :

@echo off
Color 0A & Mode 80,8
Title Get Date and Time and rename file with date
Set "File2Rename=%1"
If "%File2Rename%" == "" Goto :Help

@for %%a in ("%File2Rename%") do (
	set "FileName=%%~na"
	set "Ext=%%~xa"
)

echo(
echo( Current Name = "%FileName%%Ext%"
Call :Get_Date_Time
echo( Date : %Year%-%Month%-%Day%
echo( Time : %Hour%:%Min%:%Sec%
Set "NEW_DATE_NAME=%Month%%Day%%Year%"
echo( NEW_DATE_NAME = "%FileName%%NEW_DATE_NAME%%Ext%"
If Exist "%File2Rename%" (
REN "%File2Rename%" "%FileName%%NEW_DATE_NAME%%Ext%"
) Else (
Color 0C
echo( The file "%File2Rename%" does not exist - Please check it!
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------
:Help
Color 0C
echo(
echo      You should drag and drop an EXCEL file over, 
echo      this script "%~nx0" to be renamed !
Timeout /T 10 /NoBreak>nul
Exit
::--------------------------------------------------------------------------------------------


Reply ↓  Report •

#2
August 2, 2020 at 09:51:54
Hackoo,

Thank you for your response. Before I try this new batch file, what does “drag and drop” mean?

Thanks again,
Brian W



Reply ↓  Report •

#3
August 2, 2020 at 13:14:42
Hackoo,

I found out what "drag and drop" meant so I did it. The new batch file added the new date but did not delete the old one: TMBicycle07252020.xlsx became TMBicycle0725202008022020.xlsx.

Thanks again,
Brian W


Reply ↓  Report •

Related Solutions

#4
August 4, 2020 at 01:13:41
Ok, i updated the current batch code with a function that use regex in Powershell in order to help us to extract from string any no digits number.

@echo off
Color 0A & Mode 80,8
Title Get Date and Time and rename file with date
Set "File2Rename=%~1"
If "%File2Rename%" == "" Goto :Help

@for %%a in ("%File2Rename%") do (
	set "FileName=%%~na"
	set "Ext=%%~xa"
)
::------------------------------------------------------------
::REM Here we extract from filename only string 
::REM without any digits number with the powershell function
Call :RunPS "%FileName%" FileName
::------------------------------------------------------------
echo(
echo( Current Name = "%FileName%%Ext%"
Call :Get_Date_Time
echo( Date : %Year%-%Month%-%Day%
echo( Time : %Hour%:%Min%:%Sec%
Set "NEW_DATE_NAME=%Month%%Day%%Year%"
echo( NEW_DATE_NAME = "%FileName%%NEW_DATE_NAME%%Ext%"
If Exist "%File2Rename%" (
REN "%File2Rename%" "%FileName%%NEW_DATE_NAME%%Ext%"
) Else (
Color 0C
echo( The file "%File2Rename%" does not exist - Please check it!
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------
:Help
Color 0C
echo(
echo      You should drag and drop an EXCEL file over, 
echo      this script "%~nx0" to be renamed !
Timeout /T 10 /NoBreak>nul
Exit
::--------------------------------------------------------------------------------------------
REM This function that use regex in Powershell 
REM In order to help us to extract from string any no digits number
:RunPS <PassPSCMD> <RetValue>
Set psCmd="&{$re=[regex]'[^\d]+'; $re.Match('%1').value}"
@for /F "usebackq tokens=*" %%i in (`Powershell %psCmd%`) do set "%2=%%i"
Goto:eof
:: End of :RunPS function
::--------------------------------------------------------------------------------------------

message edited by Hackoo


Reply ↓  Report •

#5
August 4, 2020 at 06:55:08
Hackoo,

Thank you for your assistance; this new batch file works correctly. Can this batch file be modified to work with other Excel files that are always kept on the desktop? If so, what would I do?

Thanks again,
Brian W


Reply ↓  Report •

#6
August 4, 2020 at 11:28:08
✔ Best Answer
@Brian W
Check this last code to rename all "*.xlsx" on your desktop
Just copy and paste in your Notepad as RenameIT_Date.bat and execute it with double click !

@echo off
Color 0A & Mode 80,35
Title Get Date and Time and rename file with date
set "DesktopFolder=%userprofile%\Desktop"
set "Ext=xlsx"
SET /A "Count=0"
CD /D "%DesktopFolder%"
Setlocal EnableDelayedExpansion
@FOR /F "delims=" %%A IN ('dir /B "%DesktopFolder%\*.%Ext%" 2^>nul') DO (
	SET /a "Count+=1"
	SET "FileName[!Count!]=%%~nA"
	SET "Ext=%%~xA"
)

If "%Count%" EQU "0" (
	cls & Color 0C
	echo(
	Echo( There are no files with this extension "%Ext%"
	Timeout /T 3 /NoBreak>nul & Exit
) 

@For /L %%i in (1,1,%Count%) do (
	Call :RunPS "!FileName[%%i]!" NewFileName
	echo(
	echo( Current Name = "!NewFileName!!Ext!"
	Call :Get_Date_Time
	echo( Date : !Year!-!Month!-!Day!
	echo( Time : !Hour!:!Min!:!Sec!
	Set "NEW_DATE_NAME=!Month!!Day!!Year!"
	echo( NEW_DATE_NAME = "!NewFileName!!NEW_DATE_NAME!!Ext!"
	REN "!FileName[%%i]!!Ext!" "!NewFileName!!NEW_DATE_NAME!!Ext!" 2>nul
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------
:Help
Color 0C
echo(
echo      You should drag and drop an EXCEL file over, 
echo      this script "%~nx0" to be renamed !
Timeout /T 10 /NoBreak>nul
Exit
::--------------------------------------------------------------------------------------------
REM This function that use regex in Powershell 
REM In order to help us to extract from string any no digits number
:RunPS <PassPSCMD> <RetValue>
Set psCmd="&{$re=[regex]'[^^\d]+'; $re.Match('%1').value}"
@for /F "usebackq tokens=*" %%i in (`Powershell %psCmd%`) do set "%2=%%i"
Goto:eof
:: End of :RunPS function
::--------------------------------------------------------------------------------------------


Reply ↓  Report •

#7
August 6, 2020 at 07:33:06
Hackoo,

Thank you for your assistance; the text file works as expected in that it changes the date of any Excel file that is on the desktop.

Thanks again,
Brian


Reply ↓  Report •

Ask Question