Share via

Excel Automation With Coded UI Test

  • Article

The three matrix that I have validated and shared are 

  1. Excel 2013 X VS 2013 Update2
  2. Excel 2010 X VS 2013 Update2
  3. Excel 2010 X VS 2012

Install Instructions:

  1. Hit F5 on the solution. That installs the Excel Add-In
  2. Copy Microsoft.VisualStudio.Test.Sample.UI.dll to UITestExtensionPackages and PrivateAssemblies
  3. Copy Microsoft.VisualStudio.Test.Sample.UI.Excel.Communication.dll to PrivateAssemblies. 

 Important Points:  

  1. In case of VS 2013, you need update 2 to get things working
  2. Donot use two plugins at once. Pick the required set and ensure that only one addin is loaded into excel. To be sure go to Excel -> File -> Options -> Add Ins -> Manage combobox (Com Add ins) -> Go. Here remove the CodedUIAddinHelper. Open the appropriate solution from above and hit F5. This will launch excel and also loads the plugin. Subsequent launches of excel can be done from anywhere (taskbar etc)
  3. I have implemented Value as a writable properly that can be handcoded.

 

Note:

  • Path to UITestExtensionPackages will usually be something like C:\Program Files (x86)\Common Files\Microsoft Shared\VSTT\12.0\UITestExtensionPackages 
  • Path to PrivateAssemblies will usually be something like C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies

ExcelPlugins.zip

Comments

  • Anonymous
    June 03, 2014
    Thanks (^_^)

  • Anonymous
    September 29, 2014
    Hi, Thanks for the great post. In the project I am working, we have both excel 2010 and 2013 which needs to be tested. Our coded UI test is created based on 2010 visual studio and we are about to upgrade to VS 13 update 2. Is there any way to automate the tests for both versions of excel using only one solution ? It would be quite difficult to arrange the continuous integration with two different builds. Best Regards, Mon

  • Anonymous
    December 25, 2014
    Hello Thej, Thank you for the plugin. I'm using Excel 2013 X VS 2013 version. But i do not find the specified "Microsoft.VisualStudio.Test.Sample.UI.dll" (the second dll is ok) In additional, what do i need to do after finding and copying this dll? To reference these dlls in my CodedUI project? To add my CodedUI project under your solution? Thank you

  • Anonymous
    September 29, 2015
    Hello Thej, Thank for the update, the solution is really helpful! Keep it up! I use Excel 2013 and VS2013 update 5. I've compiled the solution, copied assemblies to specified locations, then unchecked CodedUIAddinHelper in Excel options and finally run your solution (Excel has been starded as you said). Up to that point everything is alright. Unfortunatelly when I try to record test using Coded UI Test Recorder, it crashes. After that I unchecked your add-in from COM Add-ins and checked CodedUIAddinHelper - now everything works fine! I can access cells while recording Coded UI test and tests pass. Do you know why is it working in such a configuration? And the most important question: Source code is distributed with no license - can I use use it and extend in my commercial project? Regards, Maciej

  • Anonymous
    February 17, 2016
    Hi, I used the plugin and generated the dlls and placed as per the post but i can't able to deduct the cell. When i deduct the error the codedui project is closed.