EG 7.11 x64 Send To Excel gives Old format or invalid type library error

[digitmaster]

Hello,

I just post this here to have some volunteers to confirm this or reject it. A support ticket was already open.
Please, be so kind to read carefully all the post before arguing or testing.

########## Begin of report #########
**Issue description:
When click on "Send To Excel" you receive  the following  error message: "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
The function "Send to Word" works properly.

**Reason:
This happens only if the OS display language [Control Panel->Region and Language->TAB Keyboards and Languages->Choose display language] does not match the Format [Control Panel->Region and Language->TAB Formats->Format]
All the other settings in Region and Language are irrelevant in the issue (e.g., the "locale" is not relevant to match).

**Very Important Note:
1- Under the same system requirements and conditions, Enterprise Guide 5.1 x64 is not affected
2- The Microsoft suggested workarounds for this Excel error as posted here https://support.microsoft.com/en-us/kb/320369 are not working or not applicable

**Affected products:
Enterprise Guide 7.11 x64, the patching level of the OS or EG  is not relevant
Maybe also EG 7.1 is affected (?)

**System requirements:  
MS Office 32 bit, 2010 or 2013  with  Multi Language Packs (MUI) installed
Windows 7 x64, with extra language packs installed
A remote SAS 9.3 server

**How to reproduce the issue:
You can test by switching the Format (logoff not required) and/or the OS Display Language (this requires a logoff) but only between languages that are installed as both MUI for MS Office and OS Language Pack (which gives you the option to change the OS Display Language). Between changes you have to close EG.    

Example: you have Win 7 in English and MS Office in English, and because the company has also German users, the workstations are deployed also with the MS Office MUI in German and the Windows 7 German Language Pack.

Assumed that your current OS Display Language and Format are English:

Open EG 7.11 x64, connect to a SAS server, open a table and "Send To Excel". This should work properly.
Close EG, change the OS Display Language (you are supposed to understand the other language) but do not change the Format, logoff; open EG 7.11 x64 "Send to Excel"; it should fail now and display the error reported above.

Close EG, go to Control Panel->Region and Language->TAB Formats->Format: German(Austria) -> Apply, OK
Open EG 7.11 x64,  "Send To Excel". This should work properly now.

**Workaround  ---->This workaround is absolutely not suggested for production
Always match the Format and the OS Display Language

NOTE:  matching the Format and OS Display Language just for some users it is in most cases not a workaround because the company Format might be required to be the same on any system (and so should be the "locale") for the current Area/Country/Nation or globally, while the OS Display Language may differ.
So, it might be that the Format must be English UK for all the users because the company office is located in London but some users need another OS Display Language. In this case the EG will give the error reported above.

########## End of report #########

I administer multicultural user groups. Some users need OS Display Language set to English and some users need it set to German, but the Format must stay "German (Austria)".
Because EG 5.1 is not affected, I cannot rollout EG 7.11 to proceed after to upgrade from SAS 9.3 to SAS 9.4:  the departments will complain for the "Send to Excel" error.
 
Regards
Digitmaster

[Kurt_Bremser]

This may not be a direct Enterprise Guide problem; it might be a problem with the MS-supplied modules for communicating with Office.

Since "Send To" usually is way less performant than saving to a proper file format and importing that into any brand of office suite, and has been proven (at least here) to work very erratically from one MS Office version to the next, I strongly discourage its use at our organisation.

I would not be surprised if the SAS people find they cannot do anything about it.

[digitmaster]

Dear Sir,

if you read carefully my detailed report you will find out that under the same software and hardware conditions EG 5.1 is not affected. It is in my opinion a bug in EG7.11 (in the code used to invoke the Excel automation) and my users like the "Send To Excel", it is there, we paid for it and we expect that SAS takes care about the problem.

I woul kindly ask any competent user here for  a technical comparison / suggestion, and not for diplomatic resolutions.

kind regards

[ChrisHemedinger]

Since you already have a Tech Support track open on this, you're already doing the proper action to lead to a resolution.

The way that this feature works: SAS Enterprise Guide launches Excel and automates the Excel interface to create a workbook, then adds all of the rows to a sheet within that workbook.  The SAS Enterprise Guide process is communicating with the Microsoft Excel process.  If the two processes have different locale settings, it's possible that some locale-sensitive values (dates, formatted number values) can get confused during the transfer.

Other methods of creating Excel sheets from EG include:

- Export data (one time) to Excel

- Export data (as a step) to Excel

- ODS Excel destination (program method in Base SAS), then download the resulting XLSX file.

Chris

[ChrisHemedinger]

Here's another thing you could try: Launch SAS Enterprise Guide with the German-Austrian locale.  You do not need the German-language resources to be installed for SAS Enterprise Guide.

../SASHome/SASEnterpriseGuide/7.1/SEGuide.exe /culture:de-AT

More on this technique here:

http://blogs.sas.com/content/sasdummy/2014/03/03/locale-specific-prompting/

Chris

[digitmaster]

Hello Chris,
thank you for your reply.

Regarding your first post: it is not related to MS product; also Send to Excel  an empty output table 2x5 gives the same error; on two identical workstations: on the one with EG 5.1 Send to Excel works properly, on the one with EG 7.11 it does not. All MS software is identical on both machines. They are clean built from the scratch just for this debug. Soon or late someone in a multilingual envoronment will complain for this.  

I'm working for a community here, not just for me, I hope you understand my spirit.

Regarding your second post:

The "culture trick " does not work because the Send To Excel gives the same error

thank you

regards

PS: it is a SAS EG bug, if it is the last thing I say.

[digitmaster]

Hello,

yesterday SAS support confirmed it as a bug and  an internal Defect-Nr. has been opened and hopefully the fix will be included in the next EG hotfix-bundle "probably" Q1 next year.

I said it was a bug.

regards

[ChrisHemedinger]

Thank you for following up. You're correct: it was a bug. This one was particular to the nuances that happen when MS Office is running in a different locale.

I see from internal notes here at SAS that the defect has been addressed in the code base for the next release. We'll dub the fix "the @Digimaster directive".

[digitmaster]

Hello Chris,

May I kindly ask you if there is any update on this? Is there any planned date to release the next hotfix?

I know that this doesn't concern anyone (it should...it should..) but I am deadly upset for this.

thank you

regards

[CaseySmith]

Hi @digitmaster,

This issue has been fixed in our upcoming EG 7.12 update, which will be available soon.  EG 7.11 will notify you when 7.12 is available and will allow you to update with one click.

Casey

[digitmaster]

Hello,
tested and confirmed: issue solved in EG 7.12 !
regards