Errors after MS Office upgrade

[tpr]

We're running 64 bit SAS 9.4 on a Windows 2008 R2 terminal server. I upgraded MS Office from 2010 to 2013 (both 32 bit) last week. Since then, the programmer on that server has been getting "CLI error trying to establish connection:" errors like the one shown in the attachment.

I have completely uninstalled and reinstalled Office 2013 and then SAS. I have given the user full rights to the ODBC registry key per this kb: http://support.sas.com/kb/40/228.html I have also installed MS Office Access Runtime 2013. I am also unable to update the Sas Add-In for Excel and the Visual C++ 2015 that I am prompted to do when I start Excel. We started seeing that error a couple of months ago, so it may not be related.

Some of the code is in the attachment. I can have the user provide more code if needed. Any ideas what I should check next? Thanks.

[JuanS_OCS]

Hello @tpr,

is Office and the ACE drivers installed on the proper bit-ness (32 bit / 64 bit ) after the upgrade, and aligned with the bit-ness expected by your engine used in the statement?

[tpr]

Hi, Juan. Yes. Office 2013 is 32 bit and the add-in is 32 bit. Is the ACE driver the runtime component? That is also 32 bit. If ACE is a different product, how do I determine its bitness and obtain a different version if needed?

Thanks.

[shayne]

Here are a few other suggestions:

• You noted that you installed MS Runtime 2013 - did you also verify that the MS Access DB Engine 2010 redistributable is installed (AccessDatabaseEngine.exe), per the 2nd to last paragraph in http://support.sas.com/kb/40/228.html?
• Does the Excel workbook have any sheets that contain links to data that's no longer available? Per last line in http://support.sas.com/kb/40/228.html; "To test for this, copy the data from the existing Excel workbook to a new Excel workbook. Then attempt to [export] the new workbook data."
• Have the user try opening the Excel file (outside of SAS) they are trying to export to and save it then try the export from SAS again. 
• Does the user have the correct permissions to export/replace the file on the specified drive? Note that replacing a file and writing a new file are different permissions in Windows, so have the user test replacing the existing file (or another existing file in the same location) to confirm that operating system permissions are not causing the issue. 

Let us know if any of the above resolve the issue for the user. 

If the issue persists, consider opening a technical support track in order to work with a SAS/ACCESS engineer on resolving this issue. 

[tpr]

Thanks, Shayne. No NTFS permissions have changed, and no files have been removed. I did discover just now that the user had full permissions to the ODBC reg key under HKEY_LOCAL_MACHINE\SOFTWARE, but not the one under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node, so I set that (thought I had last week). I'll have the user test.

Regarding the runtime versions, I thought the 2013 version would have superseded the 2010 version. I'll try installing the 2010 version and see if that makes a difference.

Thanks.

[tpr]

OK, the programmer is no longer getting the ACE error, but she is getting the DDE error shown at the bottom.

Is this related to "excel" vs "xlsx" for the file output type?

[shayne · Accepted solution]

Do you know what resolved the ACE error? The info might help other community members in the future 

For the new error: 

Is Excel open on the machine where the SAS code is executing? It can be opened using an x command but I don't see that in the code that's shown in the screen shot. See http://support.sas.com/techsup/notes/v8/26/146.html and DDE Examples from the SAS 9.4 Companion for Windows. 

If Excel is open, the code worked previously (which rules out syntax issues), and the issue persists, consider opening a technical support track. Unfortunately this isn't my particular area of expertise, but I'm sure one of the data step experts at SAS Technical Support can help you resolve it!

[tpr]

I think what fixed the ACE error was the access runtime installation and/or the ODBC reg key permissions mentioned earlier. I made both of those changes at the same time.

Opening Excel first seems to have fixed the DDE error, so we're all good again. Thanks very much for your help.

[shayne]

With regards to the other concern in your post:

I am also unable to update the Sas Add-In for Excel and the Visual C++ 2015 that I am prompted to do when I start Excel. We started seeing that error a couple of months ago, so it may not be related.

You're right and because this error started prior to the MS Office upgrade, it's likely not related. There is a known issue with downloading these updates from SAS Add-in for Microsoft Office (AMO) version 7.13 (and SAS Enterprise Guide 7.13). SAS Technical Support has a fix for this issue available that's still in the testing phase with customers - if you are running AMO 7.13, open a support track if you would like to test the fix. Otherwise a SAS Note should be published soon so keep an eye out for it!

[tpr]

OK, I'll keep my eye out for that one. Thanks.