open most recently saved file in subfolders

[rvr1982]

Hello,

This is my very first post on this forum, and I'm also new to creating batch files (if you put aside batch rendering for autodesk maya). So please excuse me for eventual mistakes or noobish questions ^^.

I have one main folder called "planning", in wich there are subfolders called "[YEAR]", in wich other subfolders called "[month]" contain each several excel sheets. Example :
.../planning/2012/January/Hello World.xlsx

I need to be able to open the latest excel sheet saved in these "sub-sub-folders" in one click.

After hours of googling, i found this code so far :

Code: Select all

@echo off & setLocal enableDELAYedeXpansioN

for /f "tokens=* delims= " %%a in ('dir/b/o-d *.xls') do (
start excel %%a
goto :eof
)

Two problems remain :
- If the most recent filename is, say, "Hello World.xlsx" (with a blank between hello and world), i'll get 2 error message :
Hello.xlsx not found and World.xlsx not found

- Also, this code searches the most recent file in the folder "planning" where the batchfile is placed in. Not in all the subfolders. Is there a way to achieve this ?

If anyone can help me out with this, i would really appreciate it, it would make me win tons of time every day.

RvR

[foxidrive]

This will work from the code you provided but only in one folder.

Code: Select all

@echo off
for /f "delims=" %%a in ('dir/b/o-d *.xls') do (
start "" excel "%%a"
goto :eof
)

To compare the file dates we need to know your regional time format - run this and reply with the exact date/time it displays.

Code: Select all

@echo off
for /f "delims=" %%a in ('dir/b/s /a-d *.xls') do (
echo %%~ta
pause
)

[rvr1982]

that would give me 23/01/2013 - 13:48

[Aacini]

You may get the last year and file with your method: get the first name in a FOR and break it whit GOTO. The problem are the month names, so they must be converted to a number.

Code: Select all

@echo off
setlocal EnableDelayedExpansion
rem Create the array to convert file names to numbers
set i=0
for %%a in (January February March April May June July August September October November December) do (
   set /A i+=1
   set month[%%a]=!i!
)
rem Get last year folder
for /F "delims=" %%a in ('dir /B /AD /O-D *') do set year=%%a& goto getMonth
:getMonth
rem Get last month folder in this year
cd %year%
set monNum=0
for /D %%a in (*) do (
   if !month[%%a]! gtr !monNum! (
      set monNum=!month[%%a]!
      set monName=%%a
   )
)
rem Get last file in this month
cd %monName%
for /F "delims=" %%a in ('dir /B /O-D *.xls') do set file=%%a& goto execute
:execute
start "" excel "%file%"

EDIT: Quotes enclosing file name added (for names with spaces).

Antonio

[foxidrive]

This works here:

Code: Select all

@echo off
del filetime.tmp 2>nul
for /f "delims=" %%a in ('dir /b /s /a-d *.xls') do (
for /f "tokens=1-5 delims=/-: " %%b in ("%%~ta") do (
echo >>filetime.tmp %%d%%c%%b%%e%%f %%a
)
)
for /f "tokens=1,*" %%x in ('sort /r ^< filetime.tmp') do (
start "" excel "%%y"
goto :done
)
:done
del filetime.tmp 2>nul

[foxidrive]

Aacini's code and mine differ markedly in that Aacini looks for the folder by date and month, and mine will take the most recent file no matter which folder it is in.

Aacini's code needs some quotes to handle the long filenames too.

[shirulkar]

Hi rvr1982,

below script will open the file in sub folder also. suppose u have folder structure as follow
c:\a\b\c1\
c:\a\b\c2\
c:\a\b\c3\
then latest saved file will open from all the folder. From folder c1, c2 and c3 also.

In below script folder name is your main folder. Ex planning

@echo off
SET count=1

FOR /F "tokens=* delims=" %%G IN ('DIR /b /s /a-d foldername') DO CALL :FileName "%%G"
set count=0
del out.txt

Exit /b

:FileName
SET filename1=%~1
IF %~dp$PATH:1 == %a%( echo >>out.txt
) ELSE (
SET count=1 )
IF %count% EQU 1 (
start "" excel "%filename1%"
set a=%~dp$PATH:1 )
set /a count = %count% + 1

EXIT /b

[rvr1982]

Hi everyone,

First of, thanks a lot for your replies, it's very interesting to see the different approaches.

The code that worked the best for me was the one of foxidrive, as i might find other uses to it than just opening our most recent planning.

All your posts made me wanna learn more of it !

Thanks again, and have a nice day

[foxidrive]

I meant to comment on this earlier: my code takes the latest file as the file which was modified most recently.
So if a file was just saved, then that will also be the file that is opened.


If you want to open the file which was created most recently then a change is needed.

[rvr1982]

That won't be necessary, as all the excel files have been saved from a same template. Therefore the creation date is identical for all of them, only the "save date" differs.

Edit.: What i just said is wrong : "created" refers to the first time it has been saved in the folder, it does not refer to the date the template has been created.
Still, as soon as a new file is saved, no changes are to be made on older ones. That means the most recent file will always be the last saved one

[shirulkar]

Hi rvr1982

Thanks for posting it was really a good topic. I also learn lot from this.