I would like to know how I can achieve the following:
Have Excel open without the request to "Enable Macros" for an Excel sheet that is distributed as a template and filled in by many, to then come back to a script of mine that wants to read the contents of the Excel sheet (Macros are only there for the users).
Any actions that require me to perform a change to my own installation of Excel (such as adding the file to the trusted docs), won't work, since the computer that runs the script, can be any of a 1000 computers that contains the automated script.
Signing the Excel sheet (something I read here, could solve the issue), is fine, but I would like to know how to go about his, when operating from within an enterprise, governed with a lot of security regulations.
I don't have access to alter the trust center settings, nor can I click the "enable macro's" button every time...
All help is welcome.
The simplest solution is to store the template in a "Trusted Location" - that is a folder on your network where macros are allowed. You can either set the template's current location as trusted, or move the template into an existing trusted location. Your IT dept should be able to set up such a location if they don't already have one enabled in people's profiles. You'll have to talk to your IT dept because it's unlikely your users can alter this setting themselves.