Why doesn't code work in VB.net, but works in VBA; GetObject

Question

VBA code works great:

Sub testVBA()

    Dim wb As Object ' Lotus123.Document
    Set wb = GetObject("S:\Temp\T\0375D.WK3", "Lotus123.Workbook")

End Sub

VB.net code fails:

Sub TestVBNet()

    Dim wb As Object ' Lotus123.Document
    wb = GetObject("S:\Temp\T\0375D.WK3", "Lotus123.Workbook")

End Sub

In VB.net I get a FileNotFoundException: "File name or class name not found during Automation operation."

As I can run it from VBA that means the file exists and that the class name exists. So why doesn't it work and how can I fix it in VB.net.

EDIT: I guess I'm not sure how to start diagnosing this: Obviously the class exists on my computer but somehow VB.net doesn't manage to find it. Maybe VB.net uses a different method to activate the class. Maybe a registry entry is missing. I am glad for any suggestions.

Edit 2: I also tried using CreateObject and got this error: "Cannot create ActiveX component." Not unexpected.

Answer 1

For some reason VB.net cannot find the class name "Lotus123.Workbook" so I tried getting the file without the class name and it works fine in XP.

Dim wb As Object ' Lotus123.Document
wb = GetObject("S:\Temp\T\0375D.WK3")

EDIT: In Win8 64bit the above doesn't work; just hangs.

The code below works in XP 32 bit as well as in Win8 64 bit. I checked with process monitor what is happening under the hood. CreateObject checks for the CLSID in the registry using the given object. Then it looks up the necessary info using the CLSID.

Public Shared Function GetLotusWB(ByVal sFile As String) As Object

    'HKCU takes precedence if exists
    'HKCU\Software\Classes\Lotus123.Workbook\CLSID
    'HKCU\Software\Classes\CLSID\{29130007-2EED-1069-BF5D-00DD011186B7}

    'normally this is used because Lotus123 doesn't create HKCU entries
    'HKCR\Lotus123.Workbook\CLSID = {29130007-2EED-1069-BF5D-00DD011186B7}
    'HKCR\CLSID\{29130007-2EED-1069-BF5D-00DD011186B7}\InprocHandler32 = ole32.dll
    'HKCR\CLSID\{29130007-2EED-1069-BF5D-00DD011186B7}\LocalServer32 = C:\Lotus\123\123w.exe

    'using object as that sometimes works better
    Dim LotusObj As Object = CreateObject("Lotus123.Workbook")

    'get application
    'need a reference to Lotus 123 else declare as Object
    Dim LotusApp As Lotus123.Application = LotusObj.Application
    'FAILS: LotusApp.Visible = True

    'open file; also works fine As Lotus123.Document
    Dim ldoc As Object = LotusApp.OpenDocument(sFile)

    'visible and activate (must declare as Object else gives exception)
    Dim appObject As Object = ldoc.Application 
    appObject.Visible = True
    ldoc.Activate()

    Return ldoc

End Function

This works great because it creates the "Lotus123.Workbook" which is used to get the application object.

Answer 2

First of all, check to make sure your inclusions (I think under Tools menu, includes or references or something like that) include the library that references Lotus123.Document. Chances are it's in the "Microsoft Excel 14.0 Object Library" or similar.

I've heard it said that VB is not VBA!

Answer 3

Load the file into an Excel workbook. It should be able to convert the lotus123 workbook on the fly.