Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Paul Gibson
  Where is Paul Gibson?
 Glasgow
 United Kingdom
 Paul Gibson
 Tags
Subject: Excel Trust Center Incorrect Error
Thread ID: 373876 Message ID: 373876 # Views: 49 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Microsoft Office Automation
Date: Thursday, March 28, 2013 12:46:09 PM         
   


After MS introduced File Blocking in Excel we had a simple strategy for getting our Excel reports out:

1. Relax the Settings in the Trust Center to allow full access to v2 Excel files.
2. COPY TO ... TYPE XLS
3. Instantiate Excel as loExcel
4. Open the v2 file
5. Do whatever manipulation is required
6. loExcel.Application.DisplayAlerts = .F.
7. loExcel.ActiveWorkbook.SaveAs(&newfile, -4143)
8. loExcel.Application.DisplayAlerts = .T.
9. loExcel.Quit() or loExcel.Visible = .T.

(our decision to output as XLS rather than XL5 is up for discussion on a different thread: Message ID: 373817)

This has been working fine until one of our users installed Excel 2013 and since Tuesday now I have been messing around trying lots of different things and seeing lots of errors and some of them are not making any sense in that the error messages contradict when the settings say.

Rewind to Tuesday afternoon: got a call from a client who said a lot of the reports were crashing their brand new machine, when I remoted onto the machine I realised that they were using Excel 2013 so I knew they were a little more advanced than the majority of our users who are still using 2007 or 2010. The first error message was on a legacy command:

loExcel.Save()

Years ago we started getting errors on that line that would say something along the lines of:

OLE IDispatch exception code 0 from Microsoft Excel: Unable to get the Save property.....

This was the error message that was returned to VFP because the DisplayAlerts setting meant that the real error message that Excel would display was suppressed. At the time we put this down to the fact that Excel 2007 and above didn't like the Save() method but worked fine with the SaveAs() method so instead of investigating the symptoms we just changed our calls to the Save() method and replaced them with calls to SaveAs() instead. That probably wasn't great analysis but it got us where we needed to be.

That got us through that little niggle and everyone appears to have been working fine using the steps at the top of this post for years.

Now that Excel 2013 is out the situation has become a lot more complicated. Excel 2013 is now crashing on the SaveAs() call with the message:

OLE IDispatch exception code 0 from Microsoft Excel: Unable to get the SaveAs property.....

On Tuesday I thought I'd cracked it. Even though the File Block settings had been relaxed to allow v2 files it wasn't until I added a particular folder (c:\int2\ - the folder which we initially export the v2 files to) to the Trusted Locations setting that Excel 2013 stopped crashing.

On Wednesday the error was back.

Being a bit more experienced and a bit more scientific, instead of just thinking that SaveAs was no longer working for Excel 2013 I took control of the users machine and started hand-rolling some Excel automation code to test things. I once again double-checked their File Block settings and Trusted Location settings and sure enough everything was relaxed and set as I had it when it was working on Tuesday afternoon but still Excel was crashing. See image for confirmation that the File Block settings are relaxed to allow Opening and Saving of all common file types.



In hand-rolling the Excel automation code I was deliberately leaving DisplayAlerts as .T. instead of changing it to .F. to suppress overwrite messages etc. This allowed me to see what the Excel itself thought the error was and to my surprise it was complaining about something that simply was not and is not true. See image:



It is complaining that it can't save "Excel 97-2003 Workbooks and Templates" because those are blocked by the File Block settings, but on checking the File Block settings again (first image) you can see that is not true. The File Block settings have never blocked "Excel 97-2003 Workbooks and Templates" either in the Open or Save column but Excel is telling me that it is.

Interestingly the error message has a link to KB article which isn't talking about File Blocking in the Trust Center but file blocking by registry policy settings:
http://support.microsoft.com/?kbid=922850
That then leads to an article about how to relax the File Block settings in Trust Center (interestingly they haven't updated the article to include Excel 2013 yet):
http://support.microsoft.com/kb/922848#e2013
following the Excel 2010 steps is exactly what I have done. There is also a link in that article to another:
http://support.microsoft.com/kb/945797
and in that the possible resolutions are:
1) Ask the system administrator to change the group setting policy.
2) Try to save the file in a file format that is not blocked by the administrator.

Those three articles just seem to link to each other without any of them explaining how to alter the settings in any way other than through the UI and changing the settings in the UI don't seem to make a difference. Really ... not ... helpful

How is all this possible, that the error message tells you to go do something that you have already done? Has anyone seen this before? Is there a way of convincing Excel 2013 that the File Block settings are not blocking the type I am attempting to save as?

I wondered if it was at all possible that something clever in Excel 2013 knew when it was trying to be automated and potentially instantiated Excel as a different user and thus the File Block settings for the "automation" user were different to the File Block settings for the "interactive" user. So I instantiated Excel with Automation, set visible to .T. so I could then check out the Automated instance of Excel's File Block settings and they were exactly the same as the "interactive" users' settings, in fact in Excel 2013 the top-right hand corner it shows the name of the user and the name is the same so my wacky idea of Excel using a different user in automation mode yielded no positive results.

So far my only realistic solution is to - I'm ruling out anything that would require me to drastically change how we output the data initially:

1. Relax the Settings in the Trust Center to allow full access to v2 Excel files.
2. COPY TO ... TYPE XLS (but could do XL5 if I decide it'll be an improvement and potentially reduce the need for step #1)
3. Instantiate Excel as loExcel
4. Open the v2 (or v5) file
5. Do whatever manipulation is required
6. loExcel.Application.DisplayAlerts = .F.
7. loExcel.ActiveWorkbook.SaveAs(&newfile2) - IMPORTANTLY newfile2 is a file path variable with xlsx as the extension, not xls and removing the file type parameter will tell Excel to save the file in it's default format. This isn't great there are still plenty of other caveats and things to do like deleting the XLS file so there aren't two copies.
8. loExcel.Application.DisplayAlerts = .T.
9. loExcel.Quit() or loExcel.Visible = .T.

That should get me through this problem but that's a hell of a lot of work to change all the points of automation in this application (hundreds - and not really a change I can apply with Code References or GoFish's help). If only Excel 2013 knew it's arse from it's elbow and could see that the File Block settings aren't blocking any file types (Whoops! little bit of frustration crept in there).

Anyone got any ideas?

ENTIRE THREAD

Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/28/2013 12:46:09 PM
RE: Excel Trust Center Incorrect Error Posted by Cetin Basoz @ 3/28/2013 2:41:52 PM
RE: Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/28/2013 6:00:47 PM
RE: Excel Trust Center Incorrect Error Posted by Anders Altberg @ 3/28/2013 7:58:00 PM
RE: Excel Trust Center Incorrect Error Posted by Paul Gibson @ 3/29/2013 10:02:56 AM