Search code examples
excelsaswindows-serverdata-connectionsvba

Refreshing Excel Data connection on a Windows Server

I am running an automated Data job on a windows server that outputs a SAS dataset everyday. On top of this, I have an Excel Report connected via Excel's Local Data Provider.

My problem is, that the Data Connection needs to be manually refreshed by a person with SAS installed on their PC. I have tried writing a piece of SAS code that opens the Excel file on the server and then triggers a piece of VBA that did the refreshing, before closing and saving. This works perfectly if I'm logged into the server. But it doesn't seem to work if nobody's signed in. I believe this has something to do with having an active user session.

UPDATE

The process that I use to connect to a local data source from Excel, looks like this:

  1. Data Connection Wizard
  2. Select SAS Local Data Provider from OLE DB Providers
  3. Add the Route to my SAS Dataset as data source
  4. Select My source table and create a Pivot Report

Is there any way to do something like this?

Solution

  • When you want to run Excel unattended, thus no interactive user is logged on, you can do this with a Windows service. You have to ensure some settings for Excel, as well as it is important how you start excel.

    Be aware of Microsoft does not support, or even suggest you to do something like this. Excel is a client software, it is exactly the opposite of a piece of software that is easy to maintain running as as service. Read this excellent article Considerations for server-side Automation of Office about the topic. Even when a little old (talking about Office 2003) it is still state of the art.

    Please consider these conditions:

    • You cannot use the System or Network account, you have to let the windows service running under an account that at least have excel started once. This cannot be done with the named ones (as they cannot be used as interactive users)
    • Excel has to be opened at least once with the user under with the windows service account will run. You'll have to configure dcom settings, open vba once to initialize it, and most probably set some settings like "trust vba model" to be able to programmatically access Excel
    • last but not least when starting Excel you have to start it with loading the user profile. The Process.Start method in c# allows exactly that by just setting a boolean value to true.

    Doing it like this, Excel should just start fine and update data connection with the VBA.