0

I want to change the default template of some Microsoft Office products on a bunch of computers. Everything is working perfectly, except for the template to be pinned at the start screen of the MS Office products.

I found the registry key to get them pinned there which saves the path of the template which I want to change with a batch file quickly.

The information about the pinned template is saved with the two registry values Item 1 and Item Metadata 1 and the path is

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates\ADAL_5DBCF6AC9A27C96B299D94D181DD223A6B8AB341FAFC42E9CFCFA1E61E3B69A6\File MRU

The data of Item 1 is:

[F00000001][T01D83DF9542BD760][O00000000]*C:\Users\test\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx

The data of Item Metadata 1 is:

<Metadata><AppSpecific><id>C:\Users\test\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx</id><nm>template</nm><du>C:\Users\test\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx</du></AppSpecific></Metadata>

The new data has a different template name and needs the %username% environment variable reference to be used to match to the logged in user.

The problem here is that the path of the key starting with ADAL_ has a random value.

I already looked up some threads where was searched for some registry keys and to delete them. So far I am able to find the keys with

reg query "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates" /s /f "Item"

I tried to get the output of the query command assigned to an environment variable to use it in a reg add command, but can't find a working method with a for /f loop, the always changing key path and the user name in the data.

Can somebody help me out on how to get a short batch script to do this?

Mofi
  • 46,139
  • 17
  • 80
  • 143
Boll
  • 3
  • 1

1 Answers1

-1

The following batch file should do the task:

@echo off
setlocal EnableExtensions DisableDelayedExpansion
for /F "tokens=1*" %%I in ('%SystemRoot%\System32\reg.exe QUERY "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates" /s /f "Item 1" 2^>nul') do if /I "%%I" == "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Recent" set "RegKey=%%I %%J" & goto UpdateRegistry
for /F "tokens=1*" %%I in ('%SystemRoot%\System32\reg.exe QUERY "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates" /s /f "File MRU" 2^>nul') do if /I "%%I" == "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Recent" set "RegKey=%%I %%J" & goto UpdateRegistry
for /F "delims=" %%I in ('%SystemRoot%\System32\reg.exe QUERY "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates" 2^>nul ^| %SystemRoot%\System32\find.exe /I "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates\ADAL_"') do set "RegKey=%%I\File MRU" & goto UpdateRegistry
echo ERROR: No "Item 1", "File MRU" or ADAL_ subkey found in Windows registry!
exit /B
:UpdateRegistry
%SystemRoot%\System32\reg.exe ADD "%RegKey%" /f /v "Item 1" /t REG_SZ /d "[F00000001][T01D83DF9542BD760][O00000000]*%USERPROFILE%\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx" >nul
%SystemRoot%\System32\reg.exe ADD "%RegKey%" /f /v "Item Metadata 1" /t REG_SZ /d "<Metadata><AppSpecific><id>%USERPROFILE%\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx</id><nm>template</nm><du>%USERPROFILE%\Documents\Benutzerdefinierte Office-Vorlagen\template.xltx</du></AppSpecific></Metadata>" >nul
endlocal

It is necessary to split up the first non-empty line into two substrings on first space character with assigning the first substring (token) to loop variable I and the rest of the line to next loop variable J to be able to verify with the IF condition doing a case-insensitive string comparison that the searched registry value Item 1 respectively registry key File MRU is found at all by the first two registry queries.

The third registry query processes just the subkeys of the specified key without searching for a registry value or a subkey at all and therefore processes always the entire output lines not being empty. Here is used FIND to make sure having found the right registry key starting with ADAL_ in the specified registry key.

It is possible that the registry key

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Recent Templates

does not exist at all because of the user has not started Excel and clicked on tab File (German: Datei) up to now. This use case results in the error message on execution of the batch script above.

Run in a command prompt window the following two commands for the reason of using %USERPROFILE%:

reg query "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders"
reg query "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders"

Read the Microsoft documentation about Using command redirection operators for an explanation of 2>nul and |. The redirection operators > and | must be escaped with caret character ^ on FOR command line to be interpreted as literal character when Windows command interpreter processes this command line before executing command FOR which executes the embedded reg command line (with find) with using a separate command process started in background.

To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.

  • echo /?
  • endlocal /?
  • exit /?
  • find /?
  • for /?
  • goto /?
  • reg /?
  • reg add /?
  • reg query /?
  • set /?
  • setlocal /?

See also single line with multiple commands using Windows batch file for an explanation of the operators & and &&.

Mofi
  • 46,139
  • 17
  • 80
  • 143
  • Thank you very much. It's working like a charm. The redirection operators was the missing part I was not aware of. – Boll Apr 04 '22 at 12:03