2

I have been coding a small thing that, rather stupidly, has been taking me a while. What it is meant to do is always have a certain excel spreadsheet (named Homework.xlsx) open between two certain times (4:30 and 5:30). The thing is it doesn't actually stay open to check for those times, even though I have it set to a loop that repeats every minute. Here is the code:

SET "stime=16:30:00.00"
SET "etime=17:30:00.00"
:start
TASKLIST /FI "IMAGENAME EQ EXCEL.EXE" 2>NUL | FIND /I /N "EXCEL.EXE" >NUL
IF %ERRORLEVEL% = 0 (
    SET Erunning=true
) ELSE (
    SET Erunning=false
)
IF EXIST C:\Users\Hunter\Documents\HOMEWORKSHEETISOPEN.txt (
    IF %Erunning%=false (
        DEL C:\Users\Hunter\Documents\HOMEWORKSHEETISOPEN.txt
    )
)
IF EXIST C:\Users\Hunter\Documents\HOMEWORKSHEETISOPEN.txt (
    SET running=true
) ELSE (
    SET running=false
)
FOR /F %%F IN ('wmic path win32_localtime get dayofweek^|Findstr [0-6]') DO SET DOW=%%F
IF %time% GEQ %stime% IF %time% LEQ %etime% IF %running%==false IF %DOW% NEQ 6 IF %DOW% NEQ 0 (
   START /MAX C:\Users\Hunter\OneDrive\Homework.xlsx
   ECHO IMRUNNING > C:\Users\Hunter\Documents\HOMEWORKSHEETISOPEN.txt 
)
TIMEOUT 60 /NOBREAK > NUL
GOTO start
BasiliskHill
  • 64
  • 1
  • 9
  • 4
    `IF %Erunning%=false (` ==> `IF %Erunning%==false (`. Same with `IF %ERRORLEVEL% = 0 (` – phuclv Mar 18 '17 at 01:40
  • where is the `time` variable defined? – phuclv Mar 18 '17 at 01:43
  • Luru, the time variable is a batch built in. Also, thanks for the first comment, that is the answer and I now have something that does it's job. – BasiliskHill Mar 18 '17 at 02:05
  • 1
    @Elven, you need to learn to TAG people properly. They will not know you responded to them unless you use an at symbol in front of their name. If you do then they get a notification when they login. – Squashman Mar 18 '17 at 03:47
  • 1
    I guess you decided not to use the code I gave you in one of your previous questions. This checks if a file is locked and in use. `2>nul ( >>test.xls (call ) ) && (echo file is not open) || (echo file is open)` – Squashman Mar 18 '17 at 03:49
  • 2
    First rule of debugging your own batch file. Do not double click on the batch file to run it. Open up a cmd prompt and run the batch file from the cmd prompt. If the batch file has an error it will show you exactly what line it stopped on and you can see the error message. – Squashman Mar 18 '17 at 03:53
  • @Squashman I had tried to understand the code that you have given but I just can't. What I have written makes sense, because I needed the knowledge of what I was doing when I was putting it together. I had been opening my file in command prompt and had already tried doing several things before I posted it here to be viewed by others. Thanks for trying to help though. – BasiliskHill Mar 18 '17 at 08:33
  • 1
    Replace test.xls with your excel file. Replace the echo commands with whatever commands you want to run. – Squashman Mar 18 '17 at 14:10

1 Answers1

1

Your code is not just convoluted, but also inefficient. If the purpose is open an excel spreadsheet in Monday..Friday @ %stime%...%etime% range, then there are simpler ways to do that. For example:

SET "stime=16:30:00.00"
SET "etime=17:30:00.00"

:start

rem Check the day
FOR /F %%F IN ('wmic path win32_localtime get dayofweek^|Findstr [0-6]') DO SET DOW=%%F
IF %DOW% EQU 0 GOTO wait1hour
IF %DOW% EQU 6 GOTO wait1hour

rem Check the time
IF %time% LSS %stime% GOTO wait1min
IF %time% GTR %etime% GOTO wait1min

rem Open the spreadsheet and wait until it is closed
START /MAX C:\Users\Hunter\OneDrive\Homework.xlsx  |  PAUSE > NUL
GOTO start

:wait1min
TIMEOUT 60 /NOBREAK > NUL
GOTO start

:wait1hour
TIMEOUT 3600 /NOBREAK > NUL
GOTO start

You may also calculate the exact number of seconds to wait if the %time% check fail, so the wait method be more efficient; I suggest you to use this method for such a calculation. You may also start the program in Mon..Fri range at the desired hour using the Windows Task Scheduler.

Community
  • 1
  • 1
Aacini
  • 65,180
  • 12
  • 72
  • 108