My first suggestion would be to check for Excel add-ins that run automatically - especially a corporate security document classification / copyright notice script.
Your next suspect is Trusted Locations, which I've mentioned elsewhere under the heading Trusted Locations: a source of misleading error messages.
This is the leading suspect, because 'Trusted Locations' is a security setting that interdicts scripts and automated COM objects, but not manual actions. Or at least, not all 'user is present' manual actions. So you won't catch it by trying to reproduce the error manually - and you might find that your attempts at debugging give inconsistent results.
You can set a folder to be a trusted location manually, in any Microsoft Office Application, under the 'File' menus as Options; Trust Center; Trusted Locations - but this is user-specific and you might not be able to do it for whoever (or whatever) runs your application. So this should give you a hint about automating it:
VBA Code to set a Trusted Location:
Public Sub TrustThisFolder(Optional FolderPath As String, _
Optional TrustSubfolders As Boolean = True, _
Optional TrustNetworkFolders As Boolean = False, _
Optional sDescription As String)
' Add a folder to the 'Trusted Locations' list so that your project's VBA can
' open Excel files without raising errors like "Office has detected a problem
' with this file. To help protect your computer this file cannot be opened."
' Ths function has been implemented to fail silently on error: if you suspect
' that users don't have permission to assign 'Trusted Location' status in all
' locations, reformulate this as a function returning True or False
' This should be used with caution: although I regard Microsoft's rationale
' for 'Trusted Locations' to be flawed (in this specific case, perverse and
' worse than ineffective) bypassing a security feature is never a good idea
' without letting the users know what they are doing, and offering a choice
' You are strongly advised to keep the confirmation dialogue, unless you've
' run into something stupid - like refusing to open files in the user's own
' local temp folder - which is what we're dealing with here, in basExcelSQL
' Author:
' Nigel Heffernan January 2015 http:\Excellerando.blogspot.com
'
' Based on code published by Daniel Pineault in DevHut.net on June 23, 2010:
' www.devhut.net\2010\06\23\vbscript-createset-trusted-location-using-vbscript\
' **** **** **** **** THIS CODE IS IN THE PUBLIC DOMAIN **** **** **** ****
' This code has been widely published, and at least one of the sites carrying
' it (and derived works) asserts that it is subject to an open-source license,
' which explicitly forbids us from asserting ownership, copyright, or other
' intellectual property rights, or attempting to impose restrictive commercial
' secrecy terms on its use, re-use, or publication. Take care to label this
' this code, and segregate it from proprietary source code, or other material
' with embedded business process information which should be kept private.
' UNIT TESTING:
'
' 1: Reinstate the commented-out line 'Debug.Print sSubKey & vbTab & sPath
' 2: Open the Immediate Window and run this command:
' TrustThisFolder "Z:\", True, True, "The user's home directory"
' 3: If "Z:\" is already in the list, choose another folder
' 4: Repeat step 2 or 3: the folder should be listed in the debug output
' 5: If it isn't listed, disable the error-handler and record any errors
On Error GoTo ErrSub
Dim sKeyPath As String
Dim oRegistry As Object
Dim sSubKey As String
Dim oSubKeys ' type not specified. After it's populated, it can be iterated
Dim oSubKey ' type not specified.
Dim bSubFolders As Boolean
Dim bNetworkLocation As Boolean
Dim iTrustNetwork As Long
Dim bTempFolder As Long
Dim sMsg As String
Dim sPath As String
Dim sDate As String
Dim sDesc As String
Dim i As Long
Const HKEY_CURRENT_USER = &H80000001
bSubFolders = True
bNetworkLocation = False
If FolderPath = "" Then
FolderPath = FSO.GetSpecialFolder(2).Path
If sDescription = "" Then
sDescription = "The user's local temp folder"
End If
bTempFolder = True
End If
If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If
sKeyPath = ""
sKeyPath = sKeyPath & "SOFTWARE\Microsoft\Office\"
sKeyPath = sKeyPath & Application.Version
sKeyPath = sKeyPath & "\Excel\Security\Trusted Locations\"
Set oRegistry = GetObject("winmgmts:\.\root\default:StdRegProv")
' Note: not the usual \root\cimv2 for WMI scripting: the StdRegProv isn't in that folder
oRegistry.EnumKey HKEY_CURRENT_USER, sKeyPath, oSubKeys
For Each oSubKey In oSubKeys
sSubKey = CStr(oSubKey)
oRegistry.GetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", sPath
'Debug.Print sSubKey & vbTab & sPath
If sPath = FolderPath Then
Exit For
End If
Next oSubKey
If sPath <> FolderPath Then
If bTempFolder = False Then
sMsg = ""
sMsg = sMsg & "Microsoft Office will not allow " & APP_NAME & " to open files from this location: "
sMsg = sMsg & vbCrLf & vbCrLf
sMsg = sMsg & vbTab & "'" & FolderPath & "'"
sMsg = sMsg & vbCrLf & vbCrLf
sMsg = sMsg & "Would you like to add this folder to Microsoft Office's list or Trusted Locations?"
Select Case MsgBox(sMsg, vbQuestion + vbYesNo, APP_NAME & ": do you trust files from this location?")
Case vbYes
' continue
Case Else ' Else captures cancel actions as well as an explicit 'No'
Err.Raise -559038737, APP_NAME & ": TrustThisFolder", "user chose not to add folder to 'Trusted Locations'"
Exit Sub ' This is dead code ...unless error-handling is bypassed. One day, you'll thank me for this.
End Select
End If
If IsNumeric(Replace(sSubKey, "Location", "")) Then
i = CLng(Replace(sSubKey, "Location", "")) + 1
Else
i = UBound(oSubKeys) + 1
End If
sSubKey = "Location" & CStr(i)
If TrustNetworkFolders Then
iTrustNetwork = 1
oRegistry.GetDWordValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", iTrustNetwork
If iTrustNetwork = 0 Then
oRegistry.SetDWordValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", 1
End If
End If
oRegistry.CreateKey HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey
oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", FolderPath
oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Description", sDescription
oRegistry.SetDWordValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "AllowSubFolders", 1
MsgPopup "Successfully added '" & FolderPath & "' to the Microsoft Office Trusted Folders list.", vbInformation, APP_NAME & ": Action confirmed.", 3
End If
ExitSub:
Set oRegistry = Nothing
Exit Sub
ErrSub:
Resume ExitSub
End Sub
Note that this code is dependent on user intervention to confirm removing the 'Trusted Location' setting. You can remove the dialogues, but I'd urge you to consult your sysadmin before you do.
(Note: I'm using 'popup' dialogue boxes, which disappear automatically after a preset interval, allowing the code to continue. Ask if you want the code)
After that, it gets arcane. Here's the code I wrote for the last time I had to use a separate instance of Excel.exe to open a file:
With New Excel.Application
.ShowStartupDialog = False
.Visible = False
.EnableCancelKey = xlDisabled
.UserControl = False
.Interactive = False
.EnableEvents = False
.DisplayAlerts = False
.AutomationSecurity = msoAutomationSecurityForceDisable
.Workbooks.Add ' Calculation property is not available if no workbooks are open
If .Calculation <> xlCalculationManual Then
.Calculation = xlCalculationManual
End If
On Error Resume Next
For i = .Workbooks.Count To 1 Step -1
.Workbooks(i).Close False
Next i
On Error Resume Next
For i = 1 To .AddIns.Count
If .AddIns(i).IsOpen Then
.AddIns(i).Installed = False
End If
Next i
For i = 1 To .COMAddIns.Count
If .COMAddIns(1).progID Like "*Information*Classification*" Then
' no action
Else
.COMAddIns(i).Connect = False
If Not .COMAddIns(i).Object Is Nothing Then
.COMAddIns(i).Object.Close
.COMAddIns(i).Object.Quit
End If
End If
Next i
End With
You'll note the 'On error Resume Next' in there: some Add-Ins can't be closed.
After that, it's phases of the moon and human sacrifice. Or, maybe, a quick look at the other answers on Stack Oveflow.
