Please Note that I have changed address
Go to
Baking Ways / Productive Bytes

Search This Blog


Thursday, September 9, 2010

How to access .Net library from VBA and VB 6.0

Download Example from Microsoft  here

Here you can find a couple of links that will explain you how to access the .Net Framework Class Libray (FCL) from VB6.0 and VBA.

This link contains a series of articles on COM and .NET
Link 1

This one shows you to use some .NET library withoug writing any line of code Link 2
In a netshell

  1. Download and install either version 1.1 or version 2.0 of the .NET Framework. If you have installed Visual Studio .NET 2003 or Visual Studio 2005, or any of the Express products, then the .NET framework is already installed.
  2. Execute Register.bat, which is included in the code download for this article. This registers the .NET framework System.dll so that it can be called as a COM object.
  3. Start Visual Basic 6.
  4. In the New Project dialog, select Standard EXE, and click OK.
  5. Add a CommandButton and Image control to the form.
  6. Set the Stretch property of the Image to true.
  7. Select the Project | References menu command.
  8. Click Browse.
  9. For v1.1 of the .NET Framework, select C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.tlb. For v2.0 of the .NET framework, select C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.tlb.
  10. Click OK
 Thies the the contens of the Register.bat file you can find on the link above.

path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;
regasm "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.dll"
regasm "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.dll"
as you can see it use the regasm utilty to register as a COM object the system.dll file.

More interestingly:

The FCL also ships with a number of powerful collection classes, which include:
  • ArrayList—An array class that doesn't have a fixed size. You can just keep adding items to it.
  • Hashtable—This class is similar to the Scripting.Dictionary class. You can add items and look them up by key.
  • Queue—This is a first in, first out (FIFO) collection. You push items in, and then read them out at a later time in the same order.
  • Stack—A first-in, last out (FILO) collection. You push items onto the stack, and then pop them off in reverse order.
  • SortedList—Similar to the Hashtable, except when you iterate through the items, they're always sorted by the key.
 To use this classe just add

a reference to either C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb or C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb, depending on the version of the framework that you want to use. Mscorlib is part of the Microsoft .NET Framework, and contains the collection classes.

and you are ready to go.

The are few caveats though
1) Intellisense will not work!!!.  To sort this out you need to write your own COM wrapper (I will tell you how to do it)
2) To use the For Each loop to run through the collection you need to define a variable as IEnumerable and assign the list to this interface. This is because the For Each loop need an explicit GetEnumerable (equal to NewEnum) method to work
    Dim objSortedList As mscorlib.SortedList
    Dim Enum As mscorlib.IEnumerable
    Set Enum = objSortedList
3) If you follow step 2 instruction you will have the For Each loop work, however the single object returned for an HashTable or a SortedList is a DictionaryEntry. This means tha VB 6.0 will not be able to access anyway its properties
All of these problems can be solved coding your own COM class in C#.

This is explained on this Link3 you can also find some info on my blog here and I will soon post some sample projects

1 comment:

  1. Great Article it its really informative and innovative keep us posted with new updates. its was really valuable. thanks a lot.
    excel vba courses london