Jeffrey Smith
asked on
Excel 2007 Save Workspace command prompts for Save of XLAM - Normal? Way to bypass?
Hi Experts,
I am finding that using Excel 2007's Save Workspace command prompts the user to Save an XLAM file I have developed. I don't t believe this behavior existed in prior Excel versions. Is this normal? And, if it is, I don't think it should be, so is there a way to bypass (i.e, don't prompt and don't Save) such files, perhaps in a modified Save Workspace command?
I expect it will require re-purposing the Save Workspace command in the:
http://schemas.microsoft.com/office/2009/07/customui
... which I can handle.
What I need assistance with is the code I would use to Save the Workspace without prompting for the Save of either xlam files or xla files (unless, perhaps, the files are open in the Excel Window by virtue of having their "IsAddin" property set to False ...).
Hope this is clear and I appreciate any insights.
Jeff
I am finding that using Excel 2007's Save Workspace command prompts the user to Save an XLAM file I have developed. I don't t believe this behavior existed in prior Excel versions. Is this normal? And, if it is, I don't think it should be, so is there a way to bypass (i.e, don't prompt and don't Save) such files, perhaps in a modified Save Workspace command?
I expect it will require re-purposing the Save Workspace command in the:
http://schemas.microsoft.com/office/2009/07/customui
... which I can handle.
What I need assistance with is the code I would use to Save the Workspace without prompting for the Save of either xlam files or xla files (unless, perhaps, the files are open in the Excel Window by virtue of having their "IsAddin" property set to False ...).
Hope this is clear and I appreciate any insights.
Jeff
ASKER
Hi SA and thanks for posting.
Your code may be getting me around that most immediate problem but as often times happens, may have morphed into another.
I am gettting this error when opening the resulting 'resume.xlw: file:
"Office File Validation detected a problem while trying to open this file. Opening it may be dangerous."
I have looked around quite a bit on the web but can't find a solution for it. Code follows:
Sub mySaveWorkspace(IRibbonCon tol, ByRef cancelDefault)
On Error Resume Next
Workbooks("myApp.xlam").Is Addin = True
Workbooks("myApp.xlam").Sa ved = True
ChDir BrowseForFolder
Application.SaveWorkspace
Err.Clear
On Error GoTo 0
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' Thanks to Ken Puls @ http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Applic ation"). _
BrowseForFolder(0, "Click 'Ok' or Scroll to choose a folder to Save your 'RESUME.XLW' Workspace file. (Referenced Files will be saved in their respective folders).", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
Any help with this error is appreciated. I would also like:
a) to know whether this behavior (Save Workspace command prompting for Save of XLAM files) is normal in Excel 2007 or 2010:
b) how to add code that prompts the user, asking if they want any unsaved files saved as part of the re-purposed Saved Workspace command.
Sorry, I won't make this question any more difficult ;--) !
Thanks,
Jeff
Your code may be getting me around that most immediate problem but as often times happens, may have morphed into another.
I am gettting this error when opening the resulting 'resume.xlw: file:
"Office File Validation detected a problem while trying to open this file. Opening it may be dangerous."
I have looked around quite a bit on the web but can't find a solution for it. Code follows:
Sub mySaveWorkspace(IRibbonCon
On Error Resume Next
Workbooks("myApp.xlam").Is
Workbooks("myApp.xlam").Sa
ChDir BrowseForFolder
Application.SaveWorkspace
Err.Clear
On Error GoTo 0
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' Thanks to Ken Puls @ http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Applic
BrowseForFolder(0, "Click 'Ok' or Scroll to choose a folder to Save your 'RESUME.XLW' Workspace file. (Referenced Files will be saved in their respective folders).", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
Any help with this error is appreciated. I would also like:
a) to know whether this behavior (Save Workspace command prompting for Save of XLAM files) is normal in Excel 2007 or 2010:
b) how to add code that prompts the user, asking if they want any unsaved files saved as part of the re-purposed Saved Workspace command.
Sorry, I won't make this question any more difficult ;--) !
Thanks,
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> First it sounds like this is a trust issue. Can you set the trust center in Excel so that this document is a trusted document?
XLW files are not 'primary' files that present within the Excel window where Trust Center settings can even be examined much less changed. The best I could do was to save the XLW Workspace file to a 'Trusted Folder' location, and executing that XLW from the Trusted Location folder DID result in NOT displaying that "Office File Validation detected a problem while trying to open this file. Opening it may be dangerous" message.
That said, it's hard to believe that MS would so change the Save Workspace command in 2007 to require the use of Trusted Folders to avoid this cryptic message ... so, again, I'm wondering is this **normal** Excel 2007 behavior? Because if it is, I'll likely put a message box to this affect in my procedure to advise users.
> So, why did you have the add in set as false previously?
I didn't necessarily have the add-in set as False but there might be a Volatile function somewhere in the project that signals a possible change. However, I also have this statement in the subject XLAM file (that prompts for a save when Save Workspace is used):
[
... so I'd think that file wouldn't prompt for a Save regardless of any possible Volatile functions.
> One thing you might want to do after the save is completed is to turn it back into a non-add in
Thanks for the code but that's not something I would want to do in this app.
I found that my previous code was not actually producing an XLW Workspace file in the chosen folder so I have revamped that to the following:
It was also interesting to me that this code does not prompt for saving any of the open Excel files, it just saves them.
So, in sum, at this point, I'm trying to determine:
a) whether it is normal behavior for Excel 2007 to display the:
"Office File Validation detected a problem while trying to open this file. Opening it may be dangerous"
... message when opening XLW Workspace files; and
b) if the only way to preclude that is to Save it in a Trusted Location folder.
Thanks,
Jeff
XLW files are not 'primary' files that present within the Excel window where Trust Center settings can even be examined much less changed. The best I could do was to save the XLW Workspace file to a 'Trusted Folder' location, and executing that XLW from the Trusted Location folder DID result in NOT displaying that "Office File Validation detected a problem while trying to open this file. Opening it may be dangerous" message.
That said, it's hard to believe that MS would so change the Save Workspace command in 2007 to require the use of Trusted Folders to avoid this cryptic message ... so, again, I'm wondering is this **normal** Excel 2007 behavior? Because if it is, I'll likely put a message box to this affect in my procedure to advise users.
> So, why did you have the add in set as false previously?
I didn't necessarily have the add-in set as False but there might be a Volatile function somewhere in the project that signals a possible change. However, I also have this statement in the subject XLAM file (that prompts for a save when Save Workspace is used):
[
indent]Private Sub Workbook_BeforeClose(Cancel As Boolean)
...
ThisWorkbook.Saved = True
...
End Sub[/indent]
... so I'd think that file wouldn't prompt for a Save regardless of any possible Volatile functions.
> One thing you might want to do after the save is completed is to turn it back into a non-add in
Thanks for the code but that's not something I would want to do in this app.
I found that my previous code was not actually producing an XLW Workspace file in the chosen folder so I have revamped that to the following:
Public folderPicked as String
Sub mySaveWorkspace(IRibbonContol, ByRef cancelDefault)
On Error Resume Next
Workbooks("myApp.xlam").IsAddin = True
Workbooks("myApp.xlam").Saved = True
GetFolder (CurDir)
ChDir folderPicked
Application.DisplayAlerts = False
Application.SaveWorkspace
Application.DisplayAlerts = True
Err.Clear
On Error GoTo 0
End Sub
Function GetFolder(InitDir As String) As String
'Thanks to Richard Schollar (& friends) at http://www.mrexcel.com/forum/showthread.php?t=294728)
Dim fldr As FileDialog
Dim sItem As String
sItem = InitDir
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select Folder to Save Workspace (Any changes in all open Excel files will be Saved to their respective folders)"
.AllowMultiSelect = False
If Right(sItem, 1) <> "\" Then
sItem = sItem & "\"
End If
.InitialFileName = sItem
If .Show <> -1 Then
sItem = InitDir
Else
sItem = .SelectedItems(1)
End If
End With
GetFolder = sItem
folderPicked = sItem
Set fldr = Nothing
End Function
It was also interesting to me that this code does not prompt for saving any of the open Excel files, it just saves them.
So, in sum, at this point, I'm trying to determine:
a) whether it is normal behavior for Excel 2007 to display the:
"Office File Validation detected a problem while trying to open this file. Opening it may be dangerous"
... message when opening XLW Workspace files; and
b) if the only way to preclude that is to Save it in a Trusted Location folder.
Thanks,
Jeff
I think this is a result of the Extension hardening that requires any mime format files to open with that prompt. If you want it to stop try turning off extension hardnening for Excel.
Important These steps may increase your security risk. These steps may also make the computer or the network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you decide to implement this process, take any appropriate additional steps to help protect the system. We recommend that you use this process only if you really require this process.
By default, a user can decide whether to open the file when the warning message is displayed. However, the user-notification function can be set to any of the following levels by using a Group Policy setting or by using Registry Editor: •Display the warning message, and do not open the file.
•Display the warning message, and let the user decide whether to open the file (This is the default setting).
•Open the file, and do not display the warning message.
Use a Group Policy setting1.Download the 2007 Office system Administrative Template files (ADM, ADMX, ADML) and Office Customization Tool from the following Microsoft Download Center Web site:
http://www.microsoft.com/downloads/thankyou.aspx?familyId=92d8519a-e143-4aee-8f7a-e4bbaeba13e7
2.Extract the administrative template files to a folder. To do this, follow these steps: •Double-click the AdminTemplates.exe file.
•Accept the Microsoft Software License Terms, and then click Continue.
•Select a folder in which to save the extracted files, and then click OK.
•Click OK after the files have been extracted successfully.
3.Open the Group Policy Object Editor.
For more information about how to open the Group Policy Object Editor, visit the following Microsoft TechNet Web site:
http://technet2.microsoft.com/windowsserver/en/library/03ec122f-fc65-496e-ad0d-4fd22a96a4bb1033.mspx
4.Expand User Configuration.
5.Right-click Administrative Templates, and then click Add/Remove Templates.
6.In the Add/Remove Templates dialog box, click Add.
7.Browse to the folder that contains the Administrative Templates that you extracted in step 2c.
8.Click the Excel12.adm file, and then click Open. The Excel12.adm file is located in the following folder to which you extracted the Administrative Templates in step 2c:
\ADM\languageID
Note The English language administrative template files are located in the EN-US languageID folder.
9.Click Close to close the Add/Remove Templates dialog box.
10.Expand Administrative Templates, expand Microsoft Office Excel 2007, expand Excel Options, and then click Security.
11.On the Extended tab, double-click Force file extension to match file type under Setting.
12.On the Setting tab, click Enabled, click one of the following in the Force file extension to match file type drop-down list, and then click OK:¿Allow different
¿Allow different, but warn
¿Always match file type
13.Close the Group Policy Object Editor.
Use Registry Editor
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.1.Exit Excel 2007.
2.Start Registry Editor.¿In Windows Vista, click Start
, type regedit in the Start Search box, and then press ENTER.
If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.
¿In Windows XP, click Start, click Run, type regedit, and then click OK.
3.Locate and then click the following registry subkey:
HKEY_CURRENT_USER\Software \Microsoft \Office\12 .0\Excel\S ecurity
4.On the Edit menu, point to New, and then click DWORD Value.
5.Type ExtensionHardening, and then press ENTER.
6.Right-click ExtensionHardening, and then click Modify.
7.In the Value data box, type the value data, and then click OK.
The following list contains the value data settings that are appropriate for the ExtensionHardening setting:¿0: Do not check the file name extension and the file type, and bypass the function of the warning message.
¿1: Check the file name extension and the file type. If they do not match, display the warning message.
¿2: Check the file name extension and the file type. If they do not match, do not open the file.
Note The default value data is 1. When the value data is set to 1, the behavior becomes the same as when no registry value is set. When the value data is set to 0, the file name extension and the file content are not checked in all situations. We do not recommend bypassing this function.
8.On the File menu, click Exit to exit Registry Editor.
Back to the top
STATUS
This behavior is by design.
Important These steps may increase your security risk. These steps may also make the computer or the network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you decide to implement this process, take any appropriate additional steps to help protect the system. We recommend that you use this process only if you really require this process.
By default, a user can decide whether to open the file when the warning message is displayed. However, the user-notification function can be set to any of the following levels by using a Group Policy setting or by using Registry Editor: •Display the warning message, and do not open the file.
•Display the warning message, and let the user decide whether to open the file (This is the default setting).
•Open the file, and do not display the warning message.
Use a Group Policy setting1.Download the 2007 Office system Administrative Template files (ADM, ADMX, ADML) and Office Customization Tool from the following Microsoft Download Center Web site:
http://www.microsoft.com/downloads/thankyou.aspx?familyId=92d8519a-e143-4aee-8f7a-e4bbaeba13e7
2.Extract the administrative template files to a folder. To do this, follow these steps: •Double-click the AdminTemplates.exe file.
•Accept the Microsoft Software License Terms, and then click Continue.
•Select a folder in which to save the extracted files, and then click OK.
•Click OK after the files have been extracted successfully.
3.Open the Group Policy Object Editor.
For more information about how to open the Group Policy Object Editor, visit the following Microsoft TechNet Web site:
http://technet2.microsoft.com/windowsserver/en/library/03ec122f-fc65-496e-ad0d-4fd22a96a4bb1033.mspx
4.Expand User Configuration.
5.Right-click Administrative Templates, and then click Add/Remove Templates.
6.In the Add/Remove Templates dialog box, click Add.
7.Browse to the folder that contains the Administrative Templates that you extracted in step 2c.
8.Click the Excel12.adm file, and then click Open. The Excel12.adm file is located in the following folder to which you extracted the Administrative Templates in step 2c:
\ADM\languageID
Note The English language administrative template files are located in the EN-US languageID folder.
9.Click Close to close the Add/Remove Templates dialog box.
10.Expand Administrative Templates, expand Microsoft Office Excel 2007, expand Excel Options, and then click Security.
11.On the Extended tab, double-click Force file extension to match file type under Setting.
12.On the Setting tab, click Enabled, click one of the following in the Force file extension to match file type drop-down list, and then click OK:¿Allow different
¿Allow different, but warn
¿Always match file type
13.Close the Group Policy Object Editor.
Use Registry Editor
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.1.Exit Excel 2007.
2.Start Registry Editor.¿In Windows Vista, click Start
, type regedit in the Start Search box, and then press ENTER.
If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.
¿In Windows XP, click Start, click Run, type regedit, and then click OK.
3.Locate and then click the following registry subkey:
HKEY_CURRENT_USER\Software
4.On the Edit menu, point to New, and then click DWORD Value.
5.Type ExtensionHardening, and then press ENTER.
6.Right-click ExtensionHardening, and then click Modify.
7.In the Value data box, type the value data, and then click OK.
The following list contains the value data settings that are appropriate for the ExtensionHardening setting:¿0: Do not check the file name extension and the file type, and bypass the function of the warning message.
¿1: Check the file name extension and the file type. If they do not match, display the warning message.
¿2: Check the file name extension and the file type. If they do not match, do not open the file.
Note The default value data is 1. When the value data is set to 1, the behavior becomes the same as when no registry value is set. When the value data is set to 0, the file name extension and the file content are not checked in all situations. We do not recommend bypassing this function.
8.On the File menu, click Exit to exit Registry Editor.
Back to the top
STATUS
This behavior is by design.
ASKER
SA,
Can you provide a link where this "Extension hardening" is associated with XLW Workspace files? My research indicates this is associated with Web files opened in Excel, not XLW Workspace files.
I don't believe that MS would provide a "Save Workspace" facility within Excel that would require users to go through all of these (potentially risky) steps to use it.
Again, can someone confirm whether it is normal behavior for Excel 2007 to display the:
... message when opening XLW Workspace files?
Jeff
Can you provide a link where this "Extension hardening" is associated with XLW Workspace files? My research indicates this is associated with Web files opened in Excel, not XLW Workspace files.
I don't believe that MS would provide a "Save Workspace" facility within Excel that would require users to go through all of these (potentially risky) steps to use it.
Again, can someone confirm whether it is normal behavior for Excel 2007 to display the:
"Office File Validation detected a problem while trying to open this file. Opening it may be dangerous"
... message when opening XLW Workspace files?
Jeff
ASKER
Further to my last post, I find that I am not the only one experiencing this inappropriate Office File Validation alert (see http://social.technet.micr osoft.com/ Forums/en/ excel/thre ad/cbd02bf f-f1cb-4cf b-8d6a-6d8 ef66d81bd? prof=requi red)
Only by uninstalling the 2007/2003 "Office File Validation Add-in" can this message be avoided (it does NOT appear in Excel 2010). (Note: Since my last post, I was prompted to install Office 2007 SP3 which I did. Now the Alert that appears makes it sound even riskier to open the file, saying that "... Opening this is probably dangerous ... " but as explained in a post I added to that referenced technet thread, the alert seems no more appropriate to XLW workspace files than it did in its pre-SP3 form.
So, back to my original reason for posting, I tested another installed XLAM and it did not prompt to be saved so I am going to check the code in the XLAM in question to see if I can find a solution here. I will report back.
Jeff
Only by uninstalling the 2007/2003 "Office File Validation Add-in" can this message be avoided (it does NOT appear in Excel 2010). (Note: Since my last post, I was prompted to install Office 2007 SP3 which I did. Now the Alert that appears makes it sound even riskier to open the file, saying that "... Opening this is probably dangerous ... " but as explained in a post I added to that referenced technet thread, the alert seems no more appropriate to XLW workspace files than it did in its pre-SP3 form.
So, back to my original reason for posting, I tested another installed XLAM and it did not prompt to be saved so I am going to check the code in the XLAM in question to see if I can find a solution here. I will report back.
Jeff
ASKER
Ok, I finally found the culprit.
When you asked: "So, why did you have the add in set as false previously?"
I answered: "I didn't necessarily have the add-in set as False but there might be a Volatile function somewhere in the project that signals a possible change. However, I also have this statement in the subject XLAM file (that prompts for a save when Save Workspace is used):
But, duh, that was only getting activated on Close of the XLAM (NOT the Save Workspace command). After reviewing my code, I found several places where I used the 'Application.Volatile' statement, and after amending that code to have a following 'ThisWorkbook.Saved = True' statement, the Save
Workspace command performed properly without prompting for a Save of my XLAM file.
So, while I found the answer myself, I'm giving credit for asking the question that led to the resolution. Thanks for your help, SA,
Jeff
When you asked: "So, why did you have the add in set as false previously?"
I answered: "I didn't necessarily have the add-in set as False but there might be a Volatile function somewhere in the project that signals a possible change. However, I also have this statement in the subject XLAM file (that prompts for a save when Save Workspace is used):
Private Sub Workbook_BeforeClose(Cance l As Boolean)
...
ThisWorkbook.Saved = True
...
End Sub
...
ThisWorkbook.Saved = True
...
End Sub
But, duh, that was only getting activated on Close of the XLAM (NOT the Save Workspace command). After reviewing my code, I found several places where I used the 'Application.Volatile' statement, and after amending that code to have a following 'ThisWorkbook.Saved = True' statement, the Save
Workspace command performed properly without prompting for a Save of my XLAM file.
So, while I found the answer myself, I'm giving credit for asking the question that led to the resolution. Thanks for your help, SA,
Jeff
Sorry, I wasn't able to respond more today I was busy at work. I'm glad you nailed it!
Open in new window
Perhaps something like:
Open in new window
or perhaps:
Open in new window
Maybe this:
Open in new window
Good Closing Resource
Hope that helps. Let me know if you need more help
-SA