Link to home
Start Free TrialLog in
Avatar of rhiancohen
rhiancohenFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Batch script to check if a registry entry exists and if so delete it

Hiya,

I need to check if certain registry values exist and if so delete them. This is how far I've got but it seems to delete all my keys where as it should be checking that the values are there and at least one should remain as it shouldn't be the path to one of the addins

I'm not sure if I'm going about this the right way or if I've missed some vital error checking or syntax

IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla" GOTO ONE
IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla" GOTO TWO
IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN2 /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xlam" GOTO THREE
IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv".xlam" GOTO FOUR
IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv".xlam" GOTO FIVE
IF EXIST REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN2 /t REG_SZ /f "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv".xlam" GOTO SIX

:ONE
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /f

:TWO
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /f

:THREE
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN2 /f

:FOUR
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /f

:FIVE
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /f

:SIX
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN2 /f

Open in new window

Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

OK, so at the moment you check for key 1 and goto key 2, it then continues to the rest of the batch, i.e. two, three, four etc.

What do you want it to do specifically, which do you want left, i.e. do you just want to lose the first one it finds?

Couple of possible options.  

1. Instead of doing "Goto" just use the reg delete command, so if it finds it delete it.  
2. Just run the delete and hide the output with reg .... >nul 2>&1 and if it finds it will delete, otherwise nothing to delete.
3. Use call :one instead.  Then after each sub use  goto :eof or exit /b - that will drop to the end of the batch and continue after the call: statement, like a "gosub" or call to a Sub/Function in other languages.

Would also suggest if you put the key name into a variable once then it is easier to read, i.e. something like this.  You can soon make it even more modularised using one call, but here is starter:

@echo off
SET key=HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\
SET ProgPath=D:\Program Files (x86)\ibm\cognos\tm1\bin

IF EXIST REG QUERY %key% /v OPEN /t REG_SZ /f "%progpath%\tm1p.xla" call :one
IF EXIST REG QUERY %key% /v OPEN1 /t REG_SZ /f "%progpath%\tm1p.xla" call :two
IF EXIST REG QUERY %key% /v OPEN2 /t REG_SZ /f "%progpath%\tm1p.xla" call :three

IF EXIST REG QUERY %key% /v OPEN /t REG_SZ /f "%progpath%\ManCalcv.xlam" call :four
IF EXIST REG QUERY %key% /v OPEN1 /t REG_SZ /f "%progpath%\ManCalcv.xlam" call :five
IF EXIST REG QUERY %key% /v OPEN2 /t REG_SZ /f "%progpath%\ManCalcv.xlam" call :six

exit /b

:ONE
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /f
exit /b

Open in new window


etc.

Steve
To modularise it more this calls a subroutine for each option to delete.  The exit /b stops it getting to the subroutine after the 6 calls.

Steve

@echo off
SET key=HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\
SET ProgPath=D:\Program Files (x86)\ibm\cognos\tm1\bin

call :FixRegistry OPEN tm1p.xla
call :FixRegistry OPEN1 tm1p.xla
call :FixRegistry OPEN2 tm1p.xla

call :FixRegistry OPEN ManCalcv.xlam
call :FixRegistry OPEN1 ManCalcv.xlam
call :FixRegistry OPEN2 ManCalcv.xlam

exit /b

:FixRegistry
  IF EXIST REG QUERY %key% /v %1 /t REG_SZ /f "%progpath%\%2" REG DELETE %key% /v %1 /f
exit /b

Open in new window

Avatar of Bill Prew
Bill Prew

Here's how I might approach it, small BAT file that uses a subroutine to check if the value exists and if so deletes it.

@echo off
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN"  "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla"
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN1" "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla"
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN2" "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xlam"
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN"  "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN1" "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
call :CleanUp "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\" "OPEN2" "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
exit /b

:CleanUp [key] [value] [data]
  reg query %~1 /v %~2 /t REG_SZ /f "%~3" >NUL 2>&1 && (
    reg delete %~1 /v %~2 /f
  )
  exit /b

Open in new window

~bp
"if exist reg query ..." will always be false. "if exist" checks if a file path exist, it does not execute commands, and neither will it determine the data of given registry value.
Did I understand you correctly that you want to compare the OPEN, OPEN1, OPEN2 keys and if in either one of these one of the value is set to "...\tm1p.xlam"  or "...\ManCalcv.xlam", then the respective key should be deleted?
Then try the script below; it's in test mode and will only display the "reg.exe delete ..."  command(s) it would normally run. Remove the uppercase ECHO in line 9 to run it for real.
Note that you first two lines spell "*.xla", while the rest is "*.xlam".
@echo off
setlocal enabledelayedexpansion
set RemoveList="D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xlam" "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
for %%a in ("OPEN" "OPEN1" "OPEN2") do (
	set Data=
	for /f "tokens=2*" %%o in ('reg.exe query "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" /v "%%~a" 2^>NUL') do set Data=%%p
	for %%r in (%RemoveList%) do (
		if /i "!Data!"=="%%~r" (
			ECHO reg.exe delete "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" /v "%%~a" /f
		)
	)
)

Open in new window

Edit: Removed erroneous closing bracket.
A good point there, durgh, didn't check the code in the sub, just the other issues of executing all the deletes.  In which case I would have used an errorlevel check like Bill using && / || to check success/fail and therefore whether to delete.

i.e.
@echo off
SET key=HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\
SET ProgPath=D:\Program Files (x86)\ibm\cognos\tm1\bin

call :FixRegistry OPEN tm1p.xla
call :FixRegistry OPEN1 tm1p.xla
call :FixRegistry OPEN2 tm1p.xla

call :FixRegistry OPEN ManCalcv.xlam
call :FixRegistry OPEN1 ManCalcv.xlam
call :FixRegistry OPEN2 ManCalcv.xlam

exit /b

:FixRegistry
  REG QUERY %key% /v %1 /t REG_SZ /f "%progpath%\%2" && REG DELETE %key% /v %1 /f
exit /b

Open in new window


Steve
Avatar of rhiancohen

ASKER

Thanks very much for your prompt replies. I won't be able to test any of these until Monday but then I will have a proper go.

I thought I'd better explain the reasoning behind this.

Basically we have 2 Remote Desktop Farms and we use roaming profiles. When users log into any VMs in the first farm, it automatically loads 2 x Excel add-ins form the D Drive of the Remote Desktop Servers in the farm.
However when they log on to the other RDS Farm, they don't want to have these add-ins and they are not available anyway as they don't exist on the D Drive of the 2nd farm so Excel complains when they open it that the paths are missing etc as they seem to roam with the profile/registry

However there was another issue where they sometimes want the default Excel Solver add-in but when you just do a straight reg delete on all the OPEN registry values on the first farm, it deletes the Excel Solver add-in if it is the OPEN, OPEN1 or OPEN2 registry path so we just needed more intelligence such as a script that looked whether OPEN, OPEN1 and OPEN2 had certain paths as the key value and if so delete them thus leaving the Solver Addin as whatever OPEN value it was

I hope this makes sense!
Then it seems I understood you correctly; the script in http:#a39962241 should work for you (once test mode is disabled).
You can most likely do this with group policy preferences too through your AD, using loopback processing to allocate those user side preferences based on the computer/OU.  Would need a bit of fiddling though so if happiest with the script approach go for it!

I think all three of the scripts do the same in slightly different ways.oBdA is more complicated as it does two loops through the two sets of options rather than repeating the 6 calls, mine and Bill's are similar approaches though I didn't notice your IF EXIST error until oBdA pointed it out.

Might be easier to add as a logoff script then it can clean up the profile on logoff?

Anyway kids bed times and pizza calling...

Steve
The proper way to do it is like this:


@echo off

set "key=HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\"
set "pth=D:\Program Files (x86)\ibm\cognos\tm1\bin"

for %%v in (OPEN, OPEN1, OPEN2) do (
  for %%f in (tm1p.xla, ManCalcv.xlam) do (
    reg query %key% /v %%v /t reg_sz /f "%pth%\%%f" >nul 2>&1 && reg delete %key% /v %%v /f
  )
)

Open in new window

Ah Paul we'd missed your "proper answers", where have you been!
Hiya,

I tested all these scripts and found the following

1. Steve - Your scripts ran but none of the OPEN keys deleted

2. Bill - Your script deleted all the OPEN keys but didn't leave the key with the Solver add-in

3. obDA - Tried in test mode and non test mode but none of the OPEN keys deleted

4. Paul - Your script deleted all the OPEN keys but didn't leave the key with the Solver add-in
Can you please post the output of the following command on a machine with both the an incorrect OPEN and the Solver OPEN entry (just paste it into an open command prompt):
for %a in (OPEN OPEN1 OPEN2) do @reg query "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" /v %a

Open in new window

Hiya
Output below

User generated image
As we don't know which server users may log into first the order may be either

OPEN = "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla"
OPEN1 = "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv2.xlam"
OPEN2 = "D:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM"

or

OPEN = "D:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM"
OPEN1 = "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla"
OPEN2 = "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv2.xlam"
The issue is that there are quotes in the string. Easy fix: at the end of line 6, "set Data=%%p", put a tilde ("~") between "%%" and "p": "set Data=%%~p"
Here's the complete fixed code in test mode:
@echo off
setlocal enabledelayedexpansion
set RemoveList="D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xlam" "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
for %%a in ("OPEN" "OPEN1" "OPEN2") do (
	set Data=
	for /f "tokens=2*" %%o in ('reg.exe query "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" /v "%%~a" 2^>NUL') do set Data=%%~p
	for %%r in (%RemoveList%) do (
		if /i "!Data!"=="%%~r" (
			ECHO reg.exe delete "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" /v "%%~a" /f
		)
	)
)

Open in new window

And in case 1 above, where OPEN and OPEN1 would be deleted, while OPEN2 remains, do the remaining OPEN entries need to be renumbered, or does Excel not care whether the OPEN keys start with 2?
Hiya,

That script works perfectly thanks very much :-) Really pleased with that. Is there a way of running it silently in the background. I know there is a nul command, I'm just not sure where to put it. Would it also be possible to get a brief explanation of exactly how the script works. I am learning and I understand parts of the script but not all of it.

From just doing some testing now, it looks like the following happens..

Log on to first server and add in tm1p.xla then ManCalcv2.xlam then the solver add-in (this is done by a PowerShell script which doesn't assign any specific OPEN paths, just adds the addins) and the registry looks like the below

OPEN = "D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla"
OPEN1 = "D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv2.xlam"
OPEN2 = "D:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM"

Log off this server and log on to the other farm and run your script and the registry then looks like the below

OPEN2 = "D:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM"

When you open Excel and close it again on this server without logging off, Excel changes the Solver add-in from OPEN2 to OPEN

OPEN = "D:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM"
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fantastic thanks ever so much and thanks for the resource site as well. I'll check it out.

Thankyou very much to everyone else who contributed also. It has been invaluable to see different ways of approaching this.

:-)
Perfect solution for us. Thanks very much