A true template is an Excel file with an XLT or XLTX extension (depends which version of Excel you are using). When you select File > New (or Office Button > New in 2007), you can select a Template as the starting point for your new workbook. Template files deployed to this location are available to all users on the Mac. In contrast, the necessary support directories for templates are not created by Office 2016 by default, so they are not likely to exist unless templates had previously been installed.
Note: I see several questions (, ) on this topic that describe how to accomplish this with the Windows version of Office, but I haven't found anything on Mac version of Office. I'm a US user, and I want to change the default date format of Excel to YYYY-MM-DD so that if I open an Excel sheet or a CSV file with dates, it displays this format in the column. (In the case of a CSV file, if the format is already YYYY-MM-DD, I don't want it switch to the default format). Alternatively, how can have this format show up as an option under Format Column - Date - US Dates? I end up having to switch my country to Belgium before I can find this option. ( @Microsoft - I'm pretty sure in 2016, people in the US use the YYYY-MM-DD format; at least make it an option! It turns out that OS X's Language & Region 'Short' date format controls how dates appear in Excel.
To modify how Excel formats ALL date columns by default to 'YYYY-MM-DD':. Go to OS X System Preferences - Language & Region - Advanced. Change the 'Short' date format by cutting-and-pasting the year to beginning. Change the separator character between the year, month, and date to -'s. Use the month and day fields' dropdown feature to select the zero-filled version (e.g., '01' if your current month is January). Restart Excel for change to take affect.
We have the volume license version of Office 2016 and I've come to really hate the default Save dialog box that is just a few blank columns with no real indication of what the user is looking at and it is very confusing for them to know what to do next. Yes, there is the 'On My Mac' button there in the lower left but if the user needs to save a file to our file server, that's not 'On My Mac'. This is a horrible design by MS and I will register my complaint with them accordingly.
To avoid the inevitable questions that this will generate, is there a 'default' command that I can roll into a Policy and set the save dialog box to 'On My Mac' mode instead of 'Online Locations' mode? Yep its in the SQLite DB, along with all other user customisable settings If you have access to MS Premiere support please log a ticket so we can put some pressure on them to change this behaviour. Creating the DB and then sending it out to all users is horrible, the DB contains a lot of other info you may not want to move between users or machines, so make sure you test thoroughly even have a look at the data in the DB with something like sqlitebrowser and perhaps test sanitising any user specific details.
So, I have been digging into the MicrosoftRegistrationDB.reg and have found the entry that controls this setting. If I use a GUI SQLite editor (DB Browser for SQLite), I can successfully change the value and get consistent results. Details: Table: HKEYCURRENTUSERSvalues nodeid: 485 name: OpenSaveLocally type: 4 value: 0 -or- 1 By default the value is 0, this will cause show the 'Online Locations' as the default save location. If you change the value to 1 it will show 'On My Mac' as the default save location. Does anyone know how we can script this change?
What you want is this: It's built into OS. Back at the PSU Mac Admins conference had a good talk covering Office 2016. When the subject of this database came up, I mentioned that one should really not copy the entire database but rather manipulate it using the appropriate tools so that you can script accordingly. Perhaps that's more than some people may want to get into. But it's certainly worthwhile picking up and learning.
Because I imagine if you copy the entire database you will be getting certain preferences that are very specific to the user you were working under. I'd look more into it myself but we're not on Office 2016 yet. Hello All, So I have been working with the sqlite3 command and have come up with a short script that will edit the OpenSaveLocally parameter. This will change the default File-Open and File-Save behavior to show the On My Mac option instead of the Online Locations. Though, the Online Locations button will still be available to the user. Note: Since this database is per-user and not per-computer, the user you want to change logged in when the script is ran. #!/bin/bash #### # sqlite3 script to change the default OpenSaveLocally from false (0) to true (1).
# By Tim Arnold # 10/13/15 #### #Gather Information loggedInUser=$(id -un) path='/Users/'$loggedInUser'/Library/Group Containers/UBF8T346G9.Office/MicrosoftRegistrationDB.reg' #Read value of current key currentKey=$(echo 'SELECT. FROM HKEYCURRENTUSERvalues WHERE name='OpenSaveLocally';' sqlite3 '$path') echo 'CurrentKey is $currentKey' #Update or insert key as needed if '$currentKey' = ' ; then echo 'No Key Present. Echo 'INSERT INTO HKEYCURRENTUSERvalues VALUES(485,'OpenSaveLocally',4,1);' sqlite3 '$path' elif $currentKey = '485 OpenSaveLocally 4 0' ; then echo 'Key Present.
Set to Online Locations. Changing to Local Default' echo 'UPDATE HKEYCURRENTUSERvalues SET value='1' WHERE name='OpenSaveLocally';' sqlite3 '$path' elif $currentKey = '485 OpenSaveLocally 4 1' ; then echo 'Key Present. Key is set to Save Locally.' Fi exit -0 Please let me know if you see anything that could be improved. I am Eric Holtam, the one is referencing. I did some rudimentary discovery back in August attempting to manipulate the.reg database prior to any users launching Office apps to have the default location not be OneDrive.
It didn't go so well. If the database exists due to a first launch of an Office app the values can be manipulated to set the default save location, but I wasn't able to pre-crate the database and settings in a way that the apps would use. If I create the database and values, the first launch will still just create its own values. I've basically given up on this.
If anyone wants to pick it up and run have at it. Notes I took while doing the discover are on -Eric. Bumping this back up. Thought I'd be clever and run a check to see if Word is running, then trigger the next policy to run the SQLite command. We have check-in every five minutes so there's a good window of opportunity to catch as many users as I can before they try to save a document. I'm no expert at bash, that's for sure. It nearly worked, but it fails because it tries to edit the DB at /Users/ root/Library.
I'm not sure how to edit the script to change this or even if it can be changed. Check if word is running. #!/bin/sh process='Word' processrunning=$(pgrep -i $process) if $processrunning!= ' then echo $process ' is running. Run policy with notification to user that Firefox will be shut down.' Jamf policy -id 44 else echo $process ' is NOT running.
Run policy without notification. Jamf policy -id ##### fi exit 0 Then make the DB changes.
#!/bin/bash #### # sqlite3 script to change the default OpenSaveLocally from false (0) to true (1). # By Tim Arnold # 10/13/15 #### #Gather Information loggedInUser=$(id -un) path='/Users/'$loggedInUser'/Library/Group Containers/UBF8T346G9.Office/MicrosoftRegistrationDB.reg' #Read value of current key currentKey=$(echo 'SELECT. FROM HKEYCURRENTUSERvalues WHERE name='OpenSaveLocally';' sqlite3 '$path') echo 'CurrentKey is $currentKey' #Update or insert key as needed if '$currentKey' = ' ; then echo 'No Key Present. Echo 'INSERT INTO HKEYCURRENTUSERvalues VALUES(485,'OpenSaveLocally',4,1);' sqlite3 '$path' elif $currentKey = '485 OpenSaveLocally 4 0' ; then echo 'Key Present. Set to Online Locations.
Changing to Local Default' echo 'UPDATE HKEYCURRENTUSERvalues SET value='1' WHERE name='OpenSaveLocally';' sqlite3 '$path' elif $currentKey = '485 OpenSaveLocally 4 1' ; then echo 'Key Present. Key is set to Save Locally.' I just started following this discussion because we're getting ready to deploy Office 2016 this coming week, and we saw this particular behavior as a potential issue for our users.
Am I understanding correctly that 's script only works if one of the apps has been launched? If that is so, and it keeps the change going forward, then we do have a partial solution. Yes, it'd be nice to pre-create this BEFORE the user (or the admin setting up a new user) logs in. But if the only requirement is that you launch one of the apps first, I think I can live with that for now. : Thanks, for the confirmation.
I'm wondering if other settings that used to be in plists are also now saved in the SQLite DB. For example: - Skip document gallery - Save AutoRecover info interval When I do a defaults read on the individual Office apps' plists, I don't see these keys. They used to be saved in the com.microsoft.office.plist file, but that file doesn't exist anymore. Regarding your fear of changing the db while one of the apps is running, isn't that what we're doing anyway when we change any settings in the Preferences window of any of the apps?
Or, in this particular case, the Save/Open dialog? I don't think it'll hurt to test it out. I wanted to update everyone here: First, I couldn't find any new solution for this problem. I kept running into the same wall - having to launch an application to initialize the DB before running the script to edit the DB key. Second, It looks like this problem may go away with the January release of Microsoft Office 2016.
From the Mac Admins - Slack group: Already scheduled for 15.18 mid-January Release (i.e. Work currently in progress). 1. New plist preference to control default save location Here's to having a great Holiday, and then coming back to a resolution from Microsoft!! Even though 15.18 is not currently released to the public. Here is the command to have office default to Open and Save Locally instead of OneDrive once it is released: defaults write /Users/$userShortName/Library/Group Containers/UBF8T346G9.Office/com.microsoft.officeprefs.plist DefaultsToLocalOpenSave -bool TRUE This is a user preference and not a computer wide preference, so it needs to be run for each user. Credit goes to Paul Bowden (Software engineer for Office for Mac/iOS at Microsoft) for this becoming a pref instead of only in the MicrosoftRegistrationDB.