Help writing a .BAT file or VBScript

Hi, i want to write a BATCH file or VBS to edit an existing BATCH file.
I have a batch file that is run to open an excel document, the bat file is as follows:

CAPWG.BAT
cd c:\docs\cashcontrol\currentyear
start excel c:\docs\cashcontrol\currentyear\wg10-STORENAME.xls
exit

Every new financial year i have to dial in remotely to each store to change the above batch file (the WG10
part of the filename refpresents the year, so this year it would need to be WG11).
I have almost 150 sites that i will need to do this change in so i am trying to speed up the process by setting up a
windows schedule to run a bat file or vbscript to make the change for me.

Can anyone help me in writing the script to change the year no please?

 
Chris_HopkinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yehudahaCommented:
storename part stay permanent ?
0
Steve KnightIT ConsultancyCommented:
Silly question but why not have one called current-storename.xls for the current year?

Or if you want to keep the year then just select the latest file:

@echo off
cd c:\docs\cashcontrol\currentyear
for /f %%a in ('dir wg??-*.xls /o-d') do start excel "%%~fa"

I know that isn't your current problem as such but might be better to move towards such a batch.  It wouldn't need the storename hard coded either then?

Steve
0
Jorge PaulinoIT Pro/DeveloperCommented:
This should do:

cd c:\docs\cashcontrol\currentyear
start excel c:\docs\cashcontrol\currentyear\wg%date:~-2,2%-STORENAME.xls
exit

0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Steve KnightIT ConsultancyCommented:
Sorry pasted the wrong code, would need to be more like this... does a dir of all wg1?-*.xls in order and takes the highest numbered one.... will work until 2019 anyway :-)  You could make it wg?? instead but if you have old documents from 1999 or before they would sort wrongly.

@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir wg1?-*.xls /o-n /b') do set filename="%%~fa"
start excel %filename%
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve KnightIT ConsultancyCommented:
jpaulino, I didn't go that route as we are talking financial years, presumably YE 31-mar?
0
Chris_HopkinsAuthor Commented:
Hi, thanks everyone for your replies...
0
Chris_HopkinsAuthor Commented:
Hi, thanks everyone for your replies...

yehudaha... The STORENAME is actually the site name so it is different in every site. (STORENAME is just used in the MASTER bat file)

Dragon-it...
The reason we didn't use CURRENT-STORENAME is because we archive each years sheet for all sites, so we would need the year number in the name. Also, for the first month of the financial year we would have both last years and this years spreafdsheet in the same directory which would mean that when the store sends in the spreadsheet for the new financial year it would overwrite last years...

Oh and our year end this year is 1st May.

So this is the code to use??

@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir wg1?-*.xls /o-n /b') do set filename="%%~fa"
start excel %filename%


0
Jorge PaulinoIT Pro/DeveloperCommented:
@dragon-it,

Probably you're  right!
0
Steve KnightIT ConsultancyCommented:
That should do it, though please test of course onyour filenames :-)

%filename% should get set to the biggest filename in order, so if there is a wg10 then it will run that, when wg11 is created it will run that etc.

Steve
0
Chris_HopkinsAuthor Commented:
I tested it but it keeps opening the wg10 sheet every time even though wg11 is there too.
If i can get that working then thats great for future years, I still have the problem of renaming the existing ones. I havnt got the time to create a bat file for all sites at the moment, so i was hoping to just edit the existing one.
Then maybe i can impliment your suggestion ready for next year.
Or is there a way of a bat file opening a spreadsheet without using the full file name?
i.e.
cd c:\docs\cashcontrol\currentyear
start excel c:\docs\cashcontrol\currentyear\wg10*.xls
exit

I know the above doesnt work, but can this be done instead? that way i could use the same bat file for all sites...
0
Steve KnightIT ConsultancyCommented:
well we can get the wg11*.xls filename for you with

@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir wg11-*.xls /b') do set filename="%%~fa"
start excel %filename%

but I think the problem with the other way might just be you need /on instead of o-n, try this from cmd.exe prompt

dir /b /on
dir /b /o-n

and you want the one that shows the last entry as the one you want.  I had it the wrong way around I think with /o-n

Steve
0
Chris_HopkinsAuthor Commented:
Excellent, thanks very much Steve.
I will create a bat file with the following script

@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir wg1?-*.xls /on /b') do set filename="%%~fa"
start excel %filename%
exit

and send it to every site. That will then resolve my original question and help maintain each site as i will only have 1 bat file rather than 150+ bat files to write! :)
Thanks.

Chris
0
Steve KnightIT ConsultancyCommented:
Don't forget to set a diary entry for May 2020 to amend it :-)

If you wanted to be prepared for another 10 ... and a check if still not found:

@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir wg1?-*.xls /on /b') do set filename=%%~fa
if "%filename%"=="" for /f "tokens=*" %%a in ('dir wg2?-*.xls /on /b') do set filename=%%~fa
if "%filename%"=="" (
echo WGxx-sitename.xls spreadsheet not found in %cd%
pause
) ELSE (
start excel "%filename%"
)
exit /b

Steve
0
Chris_HopkinsAuthor Commented:
Ha! thanks Steve!
0
Chris_HopkinsAuthor Commented:
Steve, sorry i have another question...
We use another bat file to copy the spreadsheets to another directory ready to be sent in to head office.
at the moment the bat file is specific for each store (just like the WG10 one was)
how can i write a bat file to copy all spreadsheets to another directory without specifying the exact file name.? that way i can use the same bat file  for all customers like the one you helped me earlier with.
Thanks
This is the current file we use...

@Echo off
cls
ECHO.
ECHO.
echo       You are about to send your Cash Control files To Cappers
ECHO.
ECHO.
ECHO.
ECHO.

pause

cd c:\docs\cashcontrol\currentyear

Copy c:\docs\cashcontrol\currentyear\CC10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\FF10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\RB10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\FC10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\WG10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\CS10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\GC10-master.xls C:\retail\fileout
Copy c:\docs\cashcontrol\currentyear\SA10-master.xls C:\retail\fileout

@Echo off
cls
ECHO.
0
Steve KnightIT ConsultancyCommented:
Hmm, what about this:

@echo off
cls
ECHO.
ECHO.
echo       You are about to send your Cash Control files To Cappers
ECHO.
ECHO.
ECHO.
ECHO.

pause
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir ??1?-*.xls /on /b') do copy "%%~fa" "c:\retail\fileout"
exit /b

That should run a copy command for each file based on the pattern ??-1?-*.xls

Steve
0
Chris_HopkinsAuthor Commented:
Thanks..
I tried
@echo off
cd c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir cc1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir ff1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir rb1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir fc1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir wg1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir cs1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir gc1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout
for /f "tokens=*" %%a in ('dir sa1?-*.xls /on /b') do set filename="%%~fa"
copy %filename% c:\retail\fileout


which worked, but yours is a alot neater!
Thanks
0
Steve KnightIT ConsultancyCommented:
Hmm, that will copy all ??1?- files though which I suppose would be 10,11,12 etc.

If you use

xcopy /d "%%~fa" "c:\retail\fileout"

then it would only copy any files that changes assuming they all stayed in the dest dir.

Otherwise we need to grab that latest part of the filename for one and then use that to copy:

@echo off
cd /d c:\docs\cashcontrol\currentyear
for /f "tokens=*" %%a in ('dir ??1x-*.xls /on /b') do set filename=%%a
REM take 2 characters from position 2 of name
set year=%filename:~2,2%
echo Now looking for files from year = %filename%
pause
xcopy /D "??%year%-*.xls" "C:\retail\fileout"

not tested that. let us know how you get on, glad it saved you 300 different batch files if so!!

PS not sure what is going on with this Q but it is not accepted, I guess because you also part accepted your own answer with 0 points?
0
Chris_HopkinsAuthor Commented:
hmmm not sure what i have done. And i don't know how to rectify it!!
0
Steve KnightIT ConsultancyCommented:
Can you un-close please so Chris_Hopkins can accept again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.