Chris_Hopkins
asked on
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\curren
start excel c:\docs\cashcontrol\curren
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?
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\curren
start excel c:\docs\cashcontrol\curren
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?
yehudaha
storename part stay permanent ?
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\curren
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
Or if you want to keep the year then just select the latest file:
@echo off
cd c:\docs\cashcontrol\curren
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
This should do:
cd c:\docs\cashcontrol\curren
start excel c:\docs\cashcontrol\curren
exit
cd c:\docs\cashcontrol\curren
start excel c:\docs\cashcontrol\curren
exit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jpaulino, I didn't go that route as we are talking financial years, presumably YE 31-mar?
ASKER
Hi, thanks everyone for your replies...
ASKER
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\curren
for /f "tokens=*" %%a in ('dir wg1?-*.xls /o-n /b') do set filename="%%~fa"
start excel %filename%
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\curren
for /f "tokens=*" %%a in ('dir wg1?-*.xls /o-n /b') do set filename="%%~fa"
start excel %filename%
@dragon-it,
Probably you're right!
Probably you're right!
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
%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
ASKER
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\curren
start excel c:\docs\cashcontrol\curren
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...
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\curren
start excel c:\docs\cashcontrol\curren
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent, thanks very much Steve.
I will create a bat file with the following script
@echo off
cd c:\docs\cashcontrol\curren
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
I will create a bat file with the following script
@echo off
cd c:\docs\cashcontrol\curren
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ha! thanks Steve!
ASKER
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\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
@Echo off
cls
ECHO.
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\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
Copy c:\docs\cashcontrol\curren
@Echo off
cls
ECHO.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks..
I tried
@echo off
cd c:\docs\cashcontrol\curren
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
I tried
@echo off
cd c:\docs\cashcontrol\curren
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmm not sure what i have done. And i don't know how to rectify it!!
Can you un-close please so Chris_Hopkins can accept again.