After days of research, hit and trail; today I finally got a working 64bit Excel Automation Add-In. I am using Visual Studio 2010 [.NET 4.0 client profile framework]. 

1. Create a Project using Visual Studio Office 2010 template “Excel 2010 Add-In”. [I opt for this as I need Ribbon UI too, if you don’t need that you can use Class library project as well]

2. Create a New class and follow this article from Eric ( Excel Automation AddIn in Visual studio ). This article covers 95% of stuff you need it to work. Thanks Eric your article is still valid after 3 yrs and new version than those mention there.

3. Make sure you, have a Signed Assembly. I am not sure if it is really true, but when I was manually registering my assembly in failed cases, it warn me to sign them, though it says “registration succeed”.

4. Good thing is I am creating Add-IN using 32bit machine, and even though my addin works for 64bit. I do not define any specific CPU, just use ANYCPU flag where applicable.

5. Make sure you do not check “Make COM Visible” under Advance setting of your Assembly properties. Again I am not sure, if it is true, but it works when I have it OFF.

6. Do not check “Register for COM” under the BUILD Tab of Project property in Visual Studio. We need to manually register using 64bit regasm through our Setup or command line as you like.

7. [Guess not important or relevant], I have by default Sign for ClickOnce mainfest enable. Actually it is enable for Template project by Visual studio. I keep it that way, doesn’t harm to have it.

8. Make sure to create a good GUID for Class definition as define in article. There is another GUID used in Assembly information, I try to match them and it didn’t work. When it works they have different GUID. Though have it same or different should make difference according to me.

9. I define a ProgID on top of my class like

[ProgId("EVA.Excel")]
[Guid("A4C56D50-D25D-41BA-AE3E-9BE5A56736D3")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Sound

{

}

I guess that is all what I make changes and have it working. Best of luck.