Agfdhyk

This is a bit of an abstract question but I hope someone can share some insight into this because Google isn't getting me any clear answers. I've been tasked with importing and exporting data between an Excel app and QuickBooks with a few requirements:

1. It can't use the awful QuickBooks import file functions. I've been tasked to rewrite this feature because of all the problems it's causing and, as far as I can tell, they are deprecated anyway.


2. The process must begin from within the Excel app. Using other intermediary technologies is fine as long as it can be done from a button press on an Excel form.

Here's what I've got:

[Attempt #1]

My first crack at this, I tried creating an Excel Add-In in Visual Studio and adding the QuickBooks SDK as a reference, which caused the Add-In to fail to load. I don't know exactly why, I just get a generic 'An add-in could not be found or could not be loaded' and when you click details, it details the exception:

Could not load file or assembly 'ExcelAddIn3, Version=1.0.0.0, Culture=neutral' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)





************** Exception Text **************



System.IO.FileLoadException: Could not load file or assembly 'ExcelAddIn3, Version=1.0.0.0, Culture=neutral' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

File name: 'ExcelAddIn3, Version=1.0.0.0, Culture=neutral'

   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

   at Microsoft.VisualStudio.Tools.Office.Runtime.DomainCreator.ExecuteCustomization.CreateEntryPoint(String entryPointTypeName)

   at Microsoft.VisualStudio.Tools.Office.Runtime.DomainCreator.ExecuteCustomization.Microsoft.VisualStudio.Tools.Office.Runtime.Interop.IExecuteCustomization2.LoadEntryPoints(IntPtr serviceProvider)









************** Loaded Assemblies **************

mscorlib

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3221.0 built by: NET472REL1LAST_C

    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll

----------------------------------------

Microsoft.VisualStudio.Tools.Office.Runtime

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Office.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Office.Runtime.dll

----------------------------------------

System.Core

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3221.0 built by: NET472REL1LAST_C

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll

----------------------------------------

System

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll

----------------------------------------

System.Configuration

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3056.0 built by: NET472REL1

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll

----------------------------------------

System.Xml

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3056.0 built by: NET472REL1

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll

----------------------------------------

System.Security

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3101.0 built by: NET472REL1LAST_B

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Security/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Security.dll

----------------------------------------

Microsoft.VisualStudio.Tools.Applications.Hosting

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Hosting/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Hosting.dll

----------------------------------------

Microsoft.VisualStudio.Tools.Applications.Runtime

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Runtime.dll

----------------------------------------

System.Deployment

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3056.0 built by: NET472REL1

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Deployment/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Deployment.dll

----------------------------------------

Microsoft.VisualStudio.Tools.Applications.ServerDocument

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.ServerDocument/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.ServerDocument.dll

----------------------------------------

System.Windows.Forms

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3221.0 built by: NET472REL1LAST_C

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll

----------------------------------------

System.Drawing

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3056.0 built by: NET472REL1

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll

----------------------------------------

System.Xml.Linq

    Assembly Version: 4.0.0.0

    Win32 Version: 4.7.3056.0 built by: NET472REL1

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll

----------------------------------------

Microsoft.Office.Tools

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.dll

----------------------------------------

Microsoft.Office.Tools.Excel.Implementation

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Excel.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Excel.Implementation.dll

----------------------------------------

Microsoft.Office.Tools.Common.Implementation

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.Implementation.dll

----------------------------------------

Microsoft.Office.Tools.Common

    Assembly Version: 10.0.0.0

    Win32 Version: 10.0.60828.0

    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.dll

----------------------------------------

[Attempt #2]

Still trying to use an Add-In, I've created a separate class library that references the QBSDK and adding that library as a reference in the Add-In. Same issue, generic error message saying add-in could not be found or could not be loaded.

I really don't know why and if anyone has some insight, please let me know.

[Attempt #3]

I'm starting to look into using WCF to communicate with a separate program that runs the QBSDK. When I create a stand-alone app and reference the SDK, it works just fine. I can connect to and exchange data between QB and my app. I've never used WCF, but it's giving me some hope that this can be done even if it's far more complex than I would prefer.

Here's how I'm thinking of it:

Excel app uses a custom Excel Add-in that uses WCF named pipes server to connect with a separate .NET app that uses WCF client to send qbXML data between the two end points. I don't even know if this is possible or what pitfalls I'm walking into but, aside from throwing in the towel, this seems like my best bet.

Is there another, better way that this could be done?

The most simple approach is to dump building a add-in for Office (a lot of pain – just racks up billable hours for no good reason - creates starving children around the world - don't bother).

The most simple approach is to build a COM object in .net. That com object can then be consumed by ANY software that supports com objects (office, windows scripting).

So first:
Build a working COM object in .net

Say this code:

Imports System.Runtime.InteropServices



Public Class Class1



    Function Hello()



        MsgBox("Hello World")



    End Function



End Class

DO NOT WRITE ANYTHING MORE then the above code. You do NOT need the office interop assemblies or ANYTHING ELSE referenced in the above .net project. I created a BLANK .net class for above. No additional references or anything.

If you wrote more then above, then out behind the wood shed you go – you are STEALING billable hours if you do more then above.

In above VS project:
Set project x86 (you must do this for office x32)
Check the one box “Register for COM interop (under compile options)

YOU ARE DONE!!!

Now, compile the above.

Now, in VBA in excel (say a test module), use this VBA code:

Sub TestC5()



   Dim obj     As Object



   Set obj = CreateObject("ComTest5.Class1")



   obj.Hello



End Sub

The above will work from Excel!!!!

Ok, now (and only after our above 2 lines of code works in Excel VBA???

Now, simply add a reference to the QB SDK, add your required properties and methods, and you are off to the races.

again:
Before adding SDK reference, get the above 2 lines of code working (if you can't get above working - fire all of your deveopers - they don't know how to build a COM object).

Simply get a COM object working in .net. that you can consume in Excel, Word, Access or ANY windows program that supports COM interfaces.

I do the above all the time with the QB SDK.

I have code that from Excel (or Access) is able to push invoices direction into QB – no export, no import – the invoices go directly into QuickBooks without any intermediate files.

Simple get above code working

Get the above SUPER DUPER SUPER EASY “com” object from .net working in Excel VBA.

Once done, then ADD the QBFC13 reference for .net, add the required properties and meahots - and you now have a working interface from Excel to QB.