none
What are the enhancements to microsoft.office.interop.excel version 14.0.4763.1150 over 14.0.4756.1000? RRS feed

  • Question

  • I am using Visual Studio 2010 and office interop calls to create Excel workbooks. After upgrading to Office 2010 from 2007 everything is working twice as slow. I noticed that I have version 14.0.4756.1000 of the excel 2010 interop dll so I downloaded the latest version 14.0.4763.1150 and tried to install it. It seemed to install but the dll's and c:\windows\assembly files were still the old version. I am thinking that installing the interop assemblies separately won't upgrade what was installed with Visual Studio. Is there more information about this anywhere? I have read everything I can on the internet and I can't find specifics about the latest release and how to upgrade the old version.

    Stephen Saunders

    Tuesday, June 19, 2012 6:06 PM

Answers

  • Hi Steve

    My advice would be to first go over to the Excel for Developers forum, describe what your application does, in general terms (what kinds of object model calls it makes to Excel) and ask if others have noticed significant performance hits in these areas in 2010 as compared to 2007.

    Based on my personal experience with Office 2010 applications generally (mainly Word), the answer you get will probably be a resounding, "Yes, we see it too." And it won't matter which build you're using...


    Cindy Meister, VSTO/Word MVP

    Wednesday, July 4, 2012 6:55 AM
    Moderator

All replies

  • Hi Stephen,

    Thanks for posting in the MSDN Forum.

    I think it's hard to say why about performance issue without detailed scenario. In your post, you said that "After upgrading to Office 2010 from 2007 everythingis working twice as slow." There has many possible options. Is the mean of "is working twice as slow" that every Office suite will launch duplicate thread on your PC? Or other situation on your side? And whether you have abnormal steps when you upgrade your Office?

    As the new PIA you need regist them into GAC in orde to let it visiable in Visual Studio. Please use gacutil.exe to regist your PIA. More information for gacutil 

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, June 20, 2012 5:38 AM
    Moderator
  • Hi Tom,

    Thanks for your response. By twice as slow I mean that my application is taking 10 hours to run after upgrading to 2010 and it took 5 hours to run with Excel 2007. It creates 600 workbooks containing 12,000 sheets and puts data into cells, adds formulas and does formatting. I use visual basic and I don't know anything about threads. My I.T. department did the upgrade and as far as I know it was quite normal.

    I have disabled all add-ins, disabled the customer experience program, disabled file indexing. I can duplicate the slowness on my development pc running Win 64 and Office 2010 64 bit as well as on my production pc running Win 32 and Office 2010 32 bit.

    Thanks for the gacutil link. I can't find the proper file to install though because when I run the o2010pia.msi file it seems to install but all my versions of the pia are still the old ones. In programs and features it will list the new version but I can't find the actual dlls. Searched the entire c: drive and registry. Is there a way to extract the dll out of the .msi file?

    Thanks,
    Steve


    Stephen Saunders

    Wednesday, June 20, 2012 4:38 PM
  • Hello Stephen

    Responding to your original question, there is no table of differences between the versions. Instead, here are several links to content regarding getting the correct versions installed on your system, and getting your application to speed up.

    How to fix an app using an old InterOp Assembly that can't be removed
    http://social.msdn.microsoft.com/Forums/en-US/vbinterop/thread/7be02d5c-50d8-40fc-8ece-3b91c6a14840

    Download details: Microsoft Office 2010: Primary Interop ...
    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=938FE8AD-583B-4BD7-A345-23250DC15855&displaylang=en

    Excel 2010 very slow to save to network
    http://social.technet.microsoft.com/Forums/en-US/excel/thread/a652c006-afdd-43f5-b872-bd5763bf0417

    EXCEL 2010 SO SLOW !
    http://social.technet.microsoft.com/Forums/en-US/excel/thread/22afccea-10bf-425a-a8e3-97f77f83dc8b


    Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

    Tuesday, June 26, 2012 5:22 PM
    Moderator
  • Hi Chris,

    Thanks for responding. I had already read all of the links you provided and they either don't apply (saving to network) or they are very confusing and they contradict themselves and are not detailed enough. I have followed links upon links to understand it better. One of my problems is that if you install the latest Primary Interop redistributable, I can't find where it installs to. All the dll's seems to be the old version. But maybe the "version" on the file properties is supposed to not match the "version" that you would see in the Programs and Features? maybe I should delete the assembly from the GAC first and also the one in "%ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14", before installing? And if so, should I delete the redirect policies too?

    Regards,

    Steve


    Stephen Saunders

    Wednesday, June 27, 2012 1:05 AM
  • Hi Stephen,

    Since you’re essentially starting over the answer to  your question about the redirect policies is a ’yes.’ To get a more complete picture of what happens on your dev system please look at the content in the following:

    Why is VS development not supported with multiple versions of ...
    http://blogs.msdn.com/b/andreww/archive/2007/06/08/why-is-vs-development-not-supported-with-multiple-versions-of-office.aspx

    Unification Policy - Alan Shi's Fusion Weblog - Site Home - MSDN Blogs
    http://blogs.msdn.com/b/alanshi/archive/2004/02/15/73244.aspx

    Folder Redirection in Windows 7
    http://social.technet.microsoft.com/Forums/en/winserverGP/thread/ed8e555a-5d1b-4608-9008-332aacfb85e5

    Here is a link to another site that dwells on specifying Assembly Identities from a Manifest
    SxS manager and CRT version
    http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/7d82bf33-5624-43d3-a1fa-650965442169


    Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

    • Proposed as answer by cjatmsModerator Tuesday, July 3, 2012 4:32 PM
    • Unproposed as answer by Steve61065 Tuesday, July 3, 2012 4:34 PM
    Wednesday, June 27, 2012 5:22 PM
    Moderator
  • Hi,

    I read the links and although they are interesting and useful, none of them seem helpful in my specific situation. I'd like to remove all my interop assemblies and start from scratch with the latest download. But am I to understand that I need to manually uninstall all 71 Office 2010 assemblies in the GAC? The assemblies were installed by Visual Studio or Microsoft Office, not by a separate install. here are the assemblies that the download will install.

    Assemblies included in O2010PIA.MSI

    1.  ADODB

    1. .    extensibility
    2. .    ipdmctrl
    3. .    Microsoft.mshtml
    4. .    Microsoft.Office.InfoPath.Permission
    5. .    Microsoft.Office.Interop.Access
    6. .    Microsoft.Office.interop.access.dao
    7. .    Microsoft.Office.Interop.Excel
    8. .    Microsoft.Office.Interop.Graph

    10.  Microsoft.Office.Interop.InfoPath

    11.  Microsoft.Office.Interop.InfoPath.SemiTrust

    12.  Microsoft.Office.Interop.InfoPath.Xml

    13.  Microsoft.Office.Interop.MSProject

    14.  Microsoft.Office.Interop.OneNote (Assembly version 12.0.0.0)

    15. Microsoft.Office.Interop.OneNote (Assembly version 14.0.0.0)

    16. Microsoft.Office.Interop.Outlook

    17. Microsoft.Office.Interop.OutlookViewCtl

    18. Microsoft.Office.Interop.PowerPoint

    19. Microsoft.Office.Interop.Publisher

    20. Microsoft.Office.Interop.SharePointDesigner

    21. Microsoft.Office.Interop.SharePointDesignerPage

    22. Microsoft.Office.Interop.SmartTag

    23. Microsoft.Office.Interop.Visio

    24. Microsoft.Office.Interop.Visio.SaveAsWeb

    25. Microsoft.Office.Interop.VisOcx

    26. Microsoft.Office.Interop.Word

    27. Microsoft.stdformat

    28. Microsoft.Vbe.Interop

    29. Microsoft.Vbe.Interop.Forms

    30. MSCOMCTL

    31. msdatasrc

    32. OFFICE

    33. stdole

    34. Policy.11.0.Microsoft.Office.Interop.Access

    35. Policy.11.0.Microsoft.Office.Interop.Excel

    36. Policy.11.0.Microsoft.Office.Interop.Graph

    37. Policy.11.0.Microsoft.Office.Interop.InfoPath

    38. Policy.11.0.Microsoft.Office.Interop.InfoPath.Xml

    39. Policy.11.0.Microsoft.Office.Interop.MSProject

    40. Policy.11.0.Microsoft.Office.Interop.Outlook

    41. Policy.11.0.Microsoft.Office.Interop.OutlookViewCtl

    42. Policy.11.0.Microsoft.Office.Interop.PowerPoint

    43. Policy.11.0.Microsoft.Office.Interop.Publisher

    44. Policy.11.0.Microsoft.Office.Interop.SmartTag

    45. Policy.11.0.Microsoft.Office.Interop.Visio

    46. Policy.11.0.Microsoft.Office.Interop.Visio.SaveAsWeb

    47. Policy.11.0.Microsoft.Office.Interop.VisOcx

    48. Policy.11.0.Microsoft.Office.Interop.Word

    49. Policy.11.0.Microsoft.Vbe.Interop

    50. Policy.11.0.Office

    51.  Policy.12.0.Microsoft.Office.Infopath

    52.  Policy.12.0.Microsoft.Office.Infopath.FormControl

    53.  Policy.12.0.Microsoft.Office.Infopath.Permission

    54.  Policy.12.0.Microsoft.Office.Interop.Access

    55.  Policy.12.0.Microsoft.Office.Interop.Access.Dao

    56.  Policy.12.0.Microsoft.Office.Interop.Excel

    57.  Policy.12.0.Microsoft.Office.Interop.Graph

    58.  Policy.12.0.Microsoft.Office.Interop.InfoPath

    59.  Policy.12.0.Microsoft.Office.Interop.InfoPath.Xml

    60.  Policy.12.0.Microsoft.Office.Interop.MSProject

    61.  Policy.12.0.Microsoft.Office.Interop.Outlook

    62.  Policy.12.0.Microsoft.Office.Interop.OutlookViewCtl

    63.  Policy.12.0.Microsoft.Office.Interop.PowerPoint

    64.  Policy.12.0.Microsoft.Office.Interop.Publisher

    65.  Policy.12.0.Microsoft.Office.Interop.SmartTag

    66.  Policy.12.0.Microsoft.Office.Interop.Visio

    67.  Policy.12.0.Microsoft.Office.Interop.Visio.SaveAsWeb

    68.  Policy.12.0.Microsoft.Office.Interop.VisOcx

    69.  Policy.12.0.Microsoft.Office.Interop.Word

    70.  Policy.12.0.Microsoft.Vbe.Interop

    71.  Policy.12.0.Office


    Stephen Saunders

    Tuesday, July 3, 2012 10:10 PM
  • Hi Steve

    My advice would be to first go over to the Excel for Developers forum, describe what your application does, in general terms (what kinds of object model calls it makes to Excel) and ask if others have noticed significant performance hits in these areas in 2010 as compared to 2007.

    Based on my personal experience with Office 2010 applications generally (mainly Word), the answer you get will probably be a resounding, "Yes, we see it too." And it won't matter which build you're using...


    Cindy Meister, VSTO/Word MVP

    Wednesday, July 4, 2012 6:55 AM
    Moderator
  • Thanks.

    Stephen Saunders

    Tuesday, July 10, 2012 3:23 PM
  • Hi Stephen,

    this response is very late I know, but I happened to see your post only now.

    Following this thread I found that you are generating quite a lot of workbooks. For this, Excel is extremely inefficient since it works through the whole user interface overhead in order to produce a set of compressed XML files. This makes it horribly slow.

    Did you ever consider yousing Open XML for Office? This is a .Net library offered by Microsoft. I used it for to create a small C# program and found that generating Excel workbooks works extremely fast - within less than a second per workbook. In addition, you can run your program as service without running into licensing, versioning, or stability issues which is not possible with Excel solutions. In your case I would expect such a solution to reduce processing time to less then 10 minutes on up-to-date hardware.

    Kind regards,

    Goetz



    • Edited by hellerim Sunday, April 7, 2013 10:38 PM
    Sunday, April 7, 2013 10:37 PM
  • Thank you for your reply. Others had pointed this out too and I looked into it but it seems like it would take many weeks or months to learn how to do it that way. Plus I am starting with macro enabled workbooks and I create a lot of formulas including vlookups and I use a lot of formatting. If I ever get some free time I will try it! For now, I changed my code so it can run on multiple computers at the same time so it is finishing in a reasonable amount of time.

    But I am still curious why it takes the equivalent of 100,000 instructions to populate 1 cell of raw data from an outside program into Excel. There is something fishy about that.


    Stephen Saunders

    Monday, April 8, 2013 7:37 PM
  • Hi Stephen,

    As to the issue of learning the library I’d say you’re right if you had to deal with Word. But with Excel, the situation is completely different. The problem with Open XML for Office SDK is that there is so little useful information around, but in case of Excel, there is.

    A compelling example should be … myself! After one day of hacking around with open XML it became clear that I should take a more systematic approach. When I have no clear idea of how this systematic manner should look like I start searching for others who had to master the same obstacles on their way. Don’t waste your time, build on the work of others instead! So, one very useful link I came up with is http://polymathprogrammer.com/. Its owner, Vincent Tan, a Singapore based developer, also wrote a 174-pages book I bought immediately after having read a little bit through his blog postings. This investment (70 $) paid well off: Just another day of work, and my program was up and running. And I did not even use his library he’s offering for free, although the reports I had to create were not just plain vanilla! There was grouping, there were formulas with variable referencing, there were merged cells, and there was formatting. No magic!  (Admittedly, there were no diagrams and no macros. But macros can be provided as plain text and included as CDATA for which Open XML offers functionality. You might copy it from real VBA code modules and use a small tool to create a list of say C# statements which would create the text string as a concatenation of the VBA code lines which you would then would paste into your code. As to diagrams, the code to generate them quickly can become very complex too when using the Office object model.)

    My argument for working with the Open XML SDK and not using Office object models includes some really striking points I did not mention in my posting, and some of these points are productivity, code quality, maintainability and testability. If you’re working with VBA then I would recommend to get rid of it as soon as possible. It came into age, and the language as well as the tools are far below current standards. VBA is only rudimentarily object-oriented; one of its biggest flaws is the lack of inheritance for which the presence of interfaces doesn’t compensate. Another flaw is that the VBA IDE falls hopelessly behind when compared to modern language IDEs as e.g. Visual Studio.

    I once had to maintain an Access application which was to create as set of about ten or so Excel reports for supply chain management. Each of these was implemented by a class on its own. Of course, common functionality was refactored into separate modules and classes. In the next iteration of the project, each of these reports was taken as a base to specify three or more flavors of it. Suddenly, they wanted some forty reports. Workload exploded. I had to refactor again, but as you surely know this is not well supported in the VBA IDE (if at all!), so it’s more or less an intensive and error-prone application of the search-and-replace pattern. Even with this refactoring in place there were lots of repetitive code where further dissecting into smaller procedures would not have reduced complexity because of endless parameter lists. How I missed class inheritance!

    Nethertheless, I came up with some principles which might be of interest for you:

    • Never use template workbooks. This creates dependencies on runtime environment (e.g. the path to the template), and changing one template could urge you to modify many different code modules.
    • Create classes for writing headers, worksheets, footers, data lines, calculated fields and so on.
    • Analyze your actions for the following patterns: When coding, usually you work with nested loops to narrow down the selection of the data you want to render on each level. Collect the criteria you identified on each level in selector objects which you can use as parameters to control processing. Try to makethe selector classes as general as possible. E.g. in a 3-level report with grand totals, groups, and details you would have three loops each instancing a selector for its level. The data of each selector would make it into say the WHERE clauses of some SQL SELECT statement. You’ll find that this pattern is reusable. Grand totals could be for the whole company, groups for its subdivisions, and months for a year as details, for instance. Accordingly, your selectors would convey the company on the outermost level, the divisions on the group level, and months or time spans on the detail level. This tells you how to narrow down your selection without actually dealing with the kind of data you will render, so it would be reusable, say for production data, for sales data, and for stock data. Of course, your drill-down strategies will use different criteria, but I bet that you could identify a small set of such strategies. These strategy classes could be the base to derive from when you need to be specific about the kind of data to render (i.e. you will have to trigger the creation of the SELECT FROM part of your SQL). In my situation, I came up with twelve specialized strategy classes which, if I had only inheritance, would have been based on 4 strategies only.
    • Note that flavors of a report differ usually on the way the same data is aggregated. In forecasting reports, e.g. you might have one report with a complete year’s view: Actual data for each month in the past, and forecast data for the future. Another flavor might present the accumulated past and future only, and so on. Basically, you select the same data, but you aggregate it differently. For all these flavors, you would use the same strategy. So aggregating data is orthogonal to narrowing down the selection. Take      advantage of this orthogonality. Classify aggregating and define headers, worksheets, footers, data lines, calculated fields and so on accordingly. You will come up with reusable classes. Use them to define generator classes which combine parameterized headers, worksheets, footers, data lines, calculated fields and so on with a strategy to build a specific report. In my case, I came up with 4 generator classes. The pattern I used was to inject a generator into a specialized strategy.

    There is a batch which produces about 400 concrete reports. It takes about 4 hours to complete (on a modern x64-laptop with 4 cores and hyperthreading  inside a virtual machine, since my customer is using an older office version than I am). One report takes between 20 and 40 seconds for completion. I opted for not to optimize processing time to keep the (VBA!) code maintainable, and a batch running 4 hours is not considered an issue. The part of the code which deals with report generation comprises about 40 classes and modules and about 10 interfaces. Had I had the chance to use a .Net language, the number of classes would have increased, but these classes would  be much less complex because of inheritance hierarchies, they would contain almost no repeated code, and they would be testable in isolation. Unit testing in VBA is not impossible, but very costly. Since the customer insisted on using Access thing are as they are. In resuming I would say that this project was too costly in implementation as well as in maintenance.

    For a different customer I have a similar project, not yet as “complex” as the one I described above. (I write “complex” because in a classic application programming situation, the program would not be considered really complex. But with VBA it becomes complex quickly. VBA is simply not conceived for creating maintainable code in an efficient manner.) An Access application was chosen to implement a tool. The first approach was to do it as a quick and dirty solution just to get a first version up and running. This is a situation where Office and VBA really shine. However, now the data is being migrated to SQL Server and the user interface to a browser based application. Call it consolidation since the tool is going to become strategic. This was my motivation to not implement the report in VBA but to write a small C# application as a proof of concept. The result is absolutely compelling. I get rid of Office version dependencies, in fact I do not even need to have Office installed to create the reports. I get an enormous gain in performance. And I get a rock-solid implementation capable to be run within a server.

    Now I know if the project I described first would have been imlemented by means of a .Net language, with the object-oriented structures described above in place, it would be very easy to replace those parts coded against Office model APIs with code based on the Open XML toolkit, since they are concentrated in a small set of modules.

    So to summarize: The Open Xml for Office SDK is a viable option for creating Excel reports. The learning curve is by far not as steep as you expect provided you take advantage of the available resources.

    Kind regards,

    Goetz







    • Edited by hellerim Tuesday, April 9, 2013 12:10 AM
    Monday, April 8, 2013 10:50 PM
  • Thanks so much for the information, I will try that book out.

    Regards,

    Steve


    Stephen Saunders

    Tuesday, April 9, 2013 3:58 PM