Without a doubt, one of the more interesting and powerful features of VB6 was its integration with Microsoft Office. That integration opened up a vast set of capabilities for working with text documents (Word), numbers and lists (Excel), presentations (PowerPoint), and even email (Outlook). Not for the faint of heart, you could create real chaos or power when used correctly.
For example, with just a few lines of code you can get a button on a form (warning: trivial example):
to open an Excel file and display it like this:
But what happens when you want to migrate those VB6 apps that used Office Integration to .NET? Or HTML5?
Let me answer the second part first: to migrate a VB6 app with Office functionality to HTML5 is a bit problematic. Office is "fully trusted code" because it runs on a private process inside your Windows (or Mac) PC. It (Office, that is) can access your physical resources like hard disks, DVD drives, web camera, system registry, attached devices like printers and scanners, and so on. Generally HTML applications running inside your web browser cannot.
There are some ways to work around this but that's the subject for another blog post.
To migrate your Office-integrated VB6 apps to .NET, you can use the Visual Basic Upgrade Companion (VBUC). .NET applications access Microsoft Office via OLB files (Office Library files). These files are present on any Windows computer with Microsoft Office installed. Normally when you create a new migration solution in VBUC the references are resolved automatically.
For example, when I went to migrate my trivial Office-automation sample to .NET, I saw this:
See the "x" besides the reference to EXCEL.EXE? This means VBUC can't locate the reference at the location in the .vbp file. This can be from two causes for any external reference: ether the GUID is hosed, or the path doesn't lead to the file (.dll, .tlb, .ocx, etc). In this case I "broke" the path deliberately to make the error appear.
Because VB6 is older than Keith Richards we frequently see situations where the VB6 code has been moved to a modern PC before working on the migration. Nothing wrong with that, but from time to time all the bits and pieces don't get moved as well. You can ensure you have all the references you need by building your VB6 app in the migration machine after moving. If there are missing references Visual Studio will complain and you can round up a search party to go find them. If you can build an .exe from your project you should have no problem running the VBUC.
However, you can still correct this missing reference inside VBUC. Right click on the items with a red "X" and select "Set Reference Manually. You'll get a dialog to let you browse to the reference:
Click "Browse..." to get to this file open dialog:
Notice the file types choices:
Your reference needs to be reachable and one of those file types. But what if it's not? In my case, I pointed the File.Open dialog to c:\program files\microsoft office\office14\excel.exe and I got an error:
In this case I had a double whammy: the GUID was broken and the pointer was missing some information.
If I hadn't broken the reference in the .vbp file, it would look like this:
Reference=*\G{00020813-0000-0000-C000-000000000046}#1.7#0#C:\foo\Microsoft Office\Office14\EXCEL.EXE#Microsoft Excel 14.0 Object Library
Notice the friendly name (#Microsoft Excel 14.0 Object Library)? And note: this is a VM running XP with Office 2010. A VB6 project on my Windows 10 machine referencing Excel gives me the following line in the .vbp file:
Reference=*\G{00020813-0000-0000-C000-000000000046}#1.9#0#C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE#Microsoft Excel 16.0 Object Library
Note that although the Office versions are different, as are the paths, the GUIDs are identical.
Ultimately if you hit the bug I just hit, you can (using your migration environment) create a new VB6 project, set the references you are missing from the IDE, save the project to a temp directory, edit the .vbp file in Notepad++ (or your editor of choice), and copy the relevant reference lines from the .vbp file and paste them into your "real" project .vbp file. Make sure each reference only has one line in the file describing (replace rather than duplicate them). Now the VBUC will find them when you next create the migration project:
This will now migrate fine to .NET. A different kind of reference to Excel is created: \obj\x86\Debug\Interop.Microsoft.Office.Interop.Excel
If you lose the reference from the project file that's where to look for it. If your migrated solution can't seem to find the excel classes, try removing the reference and adding it again, pointing to the path above.
One other note: after migration, you may get a compile error on the Main() method for Application.Run(CreateInstance()): basically the compiler will tell you it doesn't know whether you mean a Windows application or an Excel application. (Specifically: "Error 1 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.Office.Interop.Excel.Application'" ).
If this happens, just add the namespace:
System.Windows.Forms.Application.Run(CreateInstance());
And you should be fine.