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



Search This Blog

Pages

Tuesday, November 30, 2010

How to use build a mail system framework in Excel using OOP: Example

I will explain how to use the framework to build a new mail object using the framework I have build

Let'us suppose we want to build a mail to trade a Total Return Swap to be send to a broker

 1) Interface Layer: We first create a ITrsMailDataProvider and define its interface

Option Explicit

Public Function RetrieveBody(irsId As String, fundId As String) As Parameters

'No Code Here
End Function

Public Function RetrieveHeader(irsId As String, fundId As String) As HeaderDTO
'No Code Here
End Function

 2) DAL:  We then create a TrsMailDataExcelProvider that implements teh ITrsMailDataProvider interface. You   can use some mock data for testing. If you are getting data from Access just go ahead and create your TRSMailDataAccessProvider that implements the same interface

2) Interface Layer: Then we need to Create a IProviderFactory. This will be the abstract factory that define the abstract methods that return our interface ITrsMailDataProvider or a  IIrsMailDataProvider. The idea is that the abstract providers will be produced by your abstract factory. The business layer will use only those interface and will know nothing about which concrete provider it is in use

   This class will look like
   Class IProviderFactory
       GetTrsMailDataProvider as ITrsMailDataProvider  //only method signature
       GetIrsMailDataProvider as  IIrsMailDataProvider  //only method signature
   End Class

3) DAL Layer: We then need a class that implement the IProviderFactory. For Example a ExcelProviderFactory will take care to create of the MailDataProvider that have Excel as source.

Option Explicit

Implements IProviderFactory

Private mTrsMailDataProvider As TrsMailDataExcelProvider
Private mIrsMailDataProvider As IrsMailDataExcelProvider

Private Function IProviderFactory_GetIrsMailDataProvider() As IIrsMailDataProvider
If mIrsMailDataProvider Is Nothing Then
Set mIrsMailDataProvider = New IrsMailDataExcelProvider
Else
'Do Nothing
End If
Set IProviderFactory_GetIrsMailDataProvider = mIrsMailDataProvider

End Function

Private Function IProviderFactory_GetTrsMailDataProvider() As ITrsMailDataProvider
If mTrsMailDataProvider Is Nothing Then
Set mTrsMailDataProvider = New TrsMailDataExcelProvider
Else
'Do Nothing
End If

Set IProviderFactory_GetTrsMailDataProvider = mTrsMailDataProvider
End Function

4) BLL: Finally we will have the client of the Abstract Factory, as for the Abstract factory Pattern. We will call this class the DataProvider (this can be a static class in c# ore moduel in VBA) and it will sit in the business Layer. This Class has the main objective to
use the IProviderFactory interface to produce our Trs and Irs MailDataProvider. The diffuclt part here is that this class should istantiate a Real istance of the abstract class IProviderFactory, but as we know we want the business Layer to be agnostic of the DAL. To get this result in C# we can use reflection. We can just write a method so that it loads a .dll specified in a config file as a string, and create an istance of a class from this dll.
This way to reference to the DAL Layer are necessary. In VB 6.0 this is can be done with CreateObject.


Option Explicit

Private mFactory As IProviderFactory

Public Function IrsMail() As IIrsMailDataProvider
Set IrsMail = Factory.GetIrsMailDataProvider()
End Function

Public Function TrsMail() As ITrsMailDataProvider
Set TrsMail = Factory.GetTrsMailDataProvider()
End Function

Private Function Factory() As IProviderFactory
'Insert Code here
'This cose should use a config file and reflection to choose
'which concrete factory instantiate. Createobject could be used in VB 6.0
'to make this class decouple with the DAL Layer
'We keep things ease here.

If (mFactory Is Nothing) Then
Set mFactory = New ExcelProviderFactory 'I really should be using CreateObject
'to keep the class decoupled from the DAL
End If
Set Factory = mFactory

End Function



4) So fare we have done the following
    a) Business Layer -->  Interface Layer -->; DataBase Layer
    b) In the Interface Layer we have implemented the Model Provider Pattern and the Abstract Factory Pattern. Those class are just interface that define the methods that the business Layer can call to accesss the data
    c) In the DAL we have the real providers that just implement the provider interfaces and the real factory that just implement the factory methods.
    d) The transfer of the data between DAL and Business Layer is done using some object that are on the common layer such as MailDTO (data transfer object) or the Parameters collections (this is an utility object)
    e) The client of the abstract factory, the one we called DataProvider, is in the BLL and it stores in as a private field a reference to the IProviderFactory. This is where the magic happen: we can just switch the RealProvider withouth having to change any of the code that regards the BLL or Interface Layer.

Monday, November 29, 2010

How to get the scripting dictionary enumerator to use in the for each loop in visual basic

A common practice is writing VB 6.0 or VBA code to wrap the Collection object in order to create strongly type Collections.

An alternative to the collection object is the scripting.Dictionary object which you can find adding a reference to the Microsoft Scripting Runtime.

The Dictionary Object is an Hash Table, so it is preferred to the Collection object when you need to access elements in the collection by key.
In addtion it has few properties and methods that the Collection object is lacking.

Keys() returns all the keys as an array
Items() returns all the Items as an array
Exists(key): returns true if a key is in the dictionary.

The major draw back is that it does not have an enumerator so you cannot do something like

for Each v in objDictionary
'Do Something
End

Fortunately there is a work around. You can loop the Items or the Keys array

Dim v as variant
For Each v in objDictionary.Items
'Do Something
End

Not that being Items an array, v must be declared as a variant type.
It would be much nicer if we could loop using a strongly typed objected instead.
You can do this by wrapping the scripting.Dictionary class in a customized class and letting the Items method return a Collection object.
You can find here an example

Parameter Class

Option Explicit

Private mValue As Variant
Private mName As String
Private mFormat As String


Private Sub Class_Initialize()
Me.format = ""
Me.Name = ""
Me.value = Empty
End Sub

Public Property Get Name() As String
Name = mName
End Property

Public Property Let Name(strName As String)
mName = strName
End Property

Public Property Get value() As Variant
If IsObject(mValue) Then
Set value = mValue
Else
value = mValue
End If

End Property



Public Property Let value(varValue As Variant)

If IsObject(varValue) Then
Set mValue = varValue
Else
mValue = varValue
End If

End Property


Public Property Get format() As String
format = mFormat
End Property

Public Property Let format(strFormat As String)
mFormat = strFormat
End Property


Then create a Parameters.cls file and paste the code here.

Parameters Class


Option Explicit

Private Const ErrItemIsMissingNum = vbObjectError + 1001
Private Const ErrItemIsMissingSrc = "FFM:Parameters:Item"
Private Const ErrItemIsMissingDes = "Item is missing form the collection"

Private mKeys As Collection
Private mParsDic As Dictionary

Private Sub Class_Initialize()

Set mParsDic = New Dictionary


End Sub

Private Sub Class_Terminate()
Set mParsDic = Nothing
End Sub


Public Function Keys() As Collection
Dim v As Variant
Dim h As Collection
Set h = New Collection
For Each v In mParsDic.Keys
Call h.Add(v)
Next
Set Keys = h
End Function



Public Function Items() As Collection
Dim v As Variant
Dim h As Collection
Set h = New Collection
For Each v In mParsDic.Items
Call h.Add(v)
Next
Set Items = h

End Function

Public Function Item(Index As Variant) As Parameter

Set Item = mParsDic.Item(Index)

End Function

Public Sub Add00(Item As Parameter)
Call mParsDic.Add(Item.Name, Item)
End Sub

Public Sub Add01(Name As String, value As Variant, Optional format As String = "")
Dim objParameter As Parameter
Set objParameter = New Parameter

objParameter.Name = Name
objParameter.value = value
objParameter.format = format
Call Me.Add00(objParameter)
End Sub


Public Function Count() As Long
Count = mParsDic.Count
End Function


Public Function Remove(key As String)

mParsDic.Remove (key)

End Function


Public Function IsInCollection(Name As String) As Boolean


IsInCollection = mParsDic.Exists(Name)

End Function




Public Function Duplicate() As Parameters
'This function Create a New Parameter Collection
Dim objDuplicate As Parameters
Dim par As Parameter
Set objDuplicate = New Parameters
For Each par In Me.Items
Call objDuplicate.Add01(par.Name, par.value, par.format)
Next
Set Duplicate = objDuplicate

End Function


Once you have done that you will be able to write code like


Dim colPars As Parameters
Dim aa As Parameter, bb As Parameter, cc As Parameter
Dim h As Variant


Set colPars = New Parameters
Set aa = New Parameter
Set bb = New Parameter
Set cc = New Parameter

aa.Name = "Mario"
bb.Name = "Gennaro"
Call colPars.Add00(aa)
Call colPars.Add00(bb)


For Each cc In colPars.Items
Debug.Print cc.Name
Next

Monday, November 15, 2010

How to send a mail message in C#

Here you can find a piece of code to send mail using c#.
Please not that you need to know your Smtp Host server name to be able to sent out any mail.
In addtion I added a couple of lines to get the current user name.
You can map the current user name to an email address so that you can send the mail out from the current user mail address

ex: mailAddress = abeti then use mrblue.abeti@companyname.com as mail address

using System.Net.Mail;
using System.Security.Principal;

private void button1_Click(object sender, EventArgs e)

{
string mailAddress;
mailAddress = WindowsIdentity.GetCurrent().Name;
Console.Write(mailAddress);
MailMessage message = new MailMessage();
message.From = new MailAddress("mrblue.abeti@companyname.com");
message.To.Add(new MailAddress("mrred.pini@companyname.com"));
message.CC.Add(new MailAddress("mryellow.ciliegi@companyname.com"));
message.Subject = "Message From .Net";
message.Body = "This is the content";
string pdfPath = "C:\\test\\";
string pdfFileName = "prova.pdf";
Attachment pdfFile = new Attachment(pdfPath + pdfFileName);
SmtpClient client = new SmtpClient();
client.Host="MAILSERVER";
//client.Send(message);
}

Thursday, November 11, 2010

How to read write and save to a config file in C#

Here you can find some c# code to read/write/save to a config file in c#.
Please note that you need to add a reference to
References -> Add Reference -> System.Configuration

 and

using System.Configuration.
In Addtion you need to add an application configuration file



Application -> Add -> New Item -> Application Configuration File
Than you need to add an <appSettings> session to it

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="cnnString" value="prova"/>
    <add key="LastUpdateDate" value="10 Jan 2012"/>
  </appSettings>
</configuration>


When you run the application and debug the code you find that it is not working !! You can sse the effect when you run the exe generated in release directory.

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string cnnString;

// Get the current configuration file.
Configuration config =ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
cnnString = config.AppSettings.Settings["cnnString"].Value;
MessageBox.Show(cnnString);
config.AppSettings.Settings["cnnString"].Value = "Cavolo";
cnnString = config.AppSettings.Settings["cnnString"].Value;
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("appSettings");



}
}
}

Sunday, September 12, 2010

Exposing a .NET interface to COM as an interface



When you define a NET interface along with a ComVisible class with your interface set as the default one , VB6 hides this default interface entirely from you. Then, when you want to work with that interface in VB6, you use the name of the implementing class. 
The Default interface is set either with ComDefaultInterface attribute or if this is left out, C# will make the default interface the first Interface that the Class implements.
So if you want to see the interface in VB 6.0 you should not have any class implement directly the default interface, but an interface that derives from the default one. Remember that for COM all the interface members of the base interface should be redefined in the derived interface using the new keyword.

How to quick wrap a .NET Collection in a COM component

Here is the code on how to quickly wrap up a .NET Collection and exposed it to COM so that it has a default Item property and a working For Each Loop.

These are the main changes I did to the code compared to my previous post ( see point 9 )

2) The Class NetProva inherits directly form SortedList, while before I used encapsulation and delegation to mimic inheritance. This means that we just need to code the GetEnumerator(). This is because we need this method to return the most generalized interface Collections.IEnumerator. Note the use of the new keyword in the code. We could not ovverride otherwise we would have got a System.Collections.IDictionaryEnumerator return type. Also note that we are return the Enumerator of the Keys becasue COM does not support the DictionaryEntry type.

3) The other method that we might need to implement.ovverride is the indexer. We did not need to do anything here.


TIP
You might be tempted to omit the inheritance relationship when defining a COM
interface because the base methods need to be defined anyway and you don’t have to deal with the
mess of multiply defined members. However, don’t omit the relationship because it’s
still important for proper operation on both .NET and COM sides. Not only does it
provide the expected behavior in .NET clients using such interfaces (such as implicitly
converting an INetProva type to an IEnumerable type, but for COM as well because
a CCW makes QueryInterface calls on the derived interface succeed for any of its
base interfaces.

namespace Collections01
{
// To expose properties and methods to COM, you must declare them on the class
// interface and mark them with a DispId attribute, and implement them in the class.
// The order in which the members are declared in the interface is the
// order used for the COM vtable.
// ex:
// [DispId(1)]
// void Init(string userid , string password);
// [DispId(2)]
// bool ExecuteSelectCommand(string selCommand);

//Class Interface
[Guid("2c280db5-99d0-4b5d-a014-13f6a3dfe271"),
ComVisible(true),
InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INetProva : IEnumerable {
[DispId(-4)] //Iterator
new IEnumerator GetEnumerator();
[DispId(2)]
void Add(object key, object value);
[DispId(3)]
int Count { get; }
[DispId(4)]
void Remove(object key);
[DispId(0)] //Default Property
object this[object key] { get; set; }

}



// To expose events from your class, you must declare them on the events
// interface and mark them with a DispId attribute.
// The class should not implement this interface.

//Events Interface
[Guid("23aaa0da-ba82-48a1-95fb-789e8e061be5"),
ComVisible(true),
InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface INetProvaEvents
{
}



//The Class can also implement other interfaces. But only
//the first one will be exposed to COM.
//COM Class do not support inheritance beyond interface implementation
//Class Employees : List<Employee> is not COM compatible

//Class Implement the Class Interface
[Guid("4a37e6a5-2efc-42d5-91db-52787a258d85"),
ComVisible(true),
ClassInterface(ClassInterfaceType.None),
ComDefaultInterface(typeof(INetProva)),
ComSourceInterfaces(typeof(INetProvaEvents)),
ProgId("Collections01.NetProva")]
public class NetProva : SortedList, INetProva
{
public new IEnumerator GetEnumerator()
{
ICollection keys = this.Keys;
return (IEnumerator)keys.GetEnumerator();

}
}
}

Friday, September 10, 2010

How to Deploy a .NET COM Assembly with VS 2008 Quick Guide

This is a very quick guide on how to deploy a COM exposed .NET dll using VS 2008.
For details please visit
 Build and Deploy a .NET COM assembly
Guidelines for COM interoperability from .NET: Heath Stewart Blog
Exposing .NET Framework Compoents to COM



1) Add to the Solution a Setup Up project:
   Right-click Colution -- Add -- New Project
   Other Project Types -- Setup and Deployment -- Setup Project
   Name it

2) In Fyle System on Target machine go to Application Folder
3) Add Assembly...
4) Navitate to the MyLibrary.dll COM expose assembly that you want to deploy
5) Click Ok. The .tlb file associate to the .dll will be imported too. The Detected Dependencied folder will show you also the Microsoft .Net Framework and the .tlb file

6) Click MyLibary.dll and in the Property Window select
    Register  = vsdraCOM
7) Click MyLibray.tlb and in the Property Window select
    Register = vsdrfCOM
 
Build the solution.

If you have a problem with the MS VS Regestry Capture utility see here

You will find in the Setup Folder a setup.exe and msi package ready to be deployed.

How to make VS 2008 to register a C# COM DLL

To make C# create and register the typelibrary go to
Project/Properties/Build tick Register for COM interop. (Reccomended Choice)

Otherwise you need to use REGASM (see here  for the asembly registration tool). The Assembly Registration tool reads the metadata within an assembly and adds the necessary entries to the registry, which allows COM clients to create .NET Framework classes transparently. This utility is necessary when you need to expose to COM e .exe (winform application) .net assembly. You can create a type libray in this way. MyAssembly.dll can be also MyAssembly.exe

It is important that you do not check the
Project/Properties/Application/Assembly Information... Make assembly COM-Visible.
This option will set [assembly: ConVisible(true)] in the AssemblyInfo.cs file
Which will make all the Class in the Project COM visible. This in practice will make C# to generate new Guids for each class on which we did not specify a Guid attribute each time we recompile, causing a registry bloat.
It is much better to set
[assembly: ConVisible(false)], and use ComVisible(true) at class level to specify which class should be visible for COM interop .

How to Register / UnRegister a C# COM Exposed DLL manually an with VS 2008

To Register a C# COM Exposed Dll you I reccomend to create a Register.Bat file with the following instruction

 ------------------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;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;

REM we first Unregister the .NET dll and then register it
regasm.exe /u "FullPath\MyLibrary.dll" /tlb
regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb
pause

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

This insturction set the search path for the regasm.exe file. I have included the most common directory that should have it

path=%path%; .......

This line unregister the Dll and its typelibrary

regasm.exe /u "FullPath\MyLibrary.dll" /tlb


This one creates the type library and register the dll with its full Path and along the type library.
If you do not use the /codebase option VB will complain that it cannot find the library.

regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb

 To unregister make an unregister.bat file
 ---------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;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;

REM we Unregister the .NET dll
regasm.exe /u "FullPath\MyLibrary.dll" /tlb

pause

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


When you use the  code base option you need to have a strongly type Assembly (Dll) To do this
follow these steps

Go to Properties/Signing. Tick Sign the assembly check box.
Then go to Choose a stroing name key file
Choose a name  ex   "MyLibrary_COM_Key"
This will create a MyLibray_COM_Key.snk file in the project folder.

Lastly you also need to make sure that the Assembly Version is not someting like 1.0.* which means that each time you rebuild the project the assembly name changes. Just give the assembly a version, something like 1.0.0.0 and stick with it. The call to the COM exposed DLL is also connected to the assembly version registered in the registry.

Microsoft Visual Studio Registy Capture Utility has stop working Error

If you have this error while you are trying to use VS 2008 Project Setup and Development, do the following:


  1. Locate regcap.exe here: C:\Program Files(x86)\Microsoft Visual Studio 9.0\Common7\Tools\Deployment
  2. Right click and select properties.
  3. Select Compatibility tab
  4. Check box to Run this program in compatibility mode.
  5. Select Windows Vista SP2 in the OS drop-down and Run as Administrator.
  6. Click Ok and Recompile.
his also works for 64 bit Windows 7 with VS 2010. The path for regcap is C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Tools\Deployment 

Thursday, September 9, 2010

MS scripting run time and MS Regular Expression in VB 6.0 or VBA

Hi,
Two nice library that help you speed up your VB 6.0 / VBA development time are the
1) Microsoft Scripting Run Time library

   The two most interestinjg objects here are the
   Dictionary object which is an hashtable. Its only major draw back  is that it does not expose the   NewEnum function, which means that while you can wrap it up to create a strongly type dictionary object, you cannot create a NewEnum method to loop through the collection using the For Each statement

   FileSystemObject which let you easily access to files and folders

2) Microsoft VBScript Regural Expression 5.5.

    This is a really nice library to use to test a string using regular expression.
    This is an example on how it works:





Function TestRegExp(myPattern As String, myString As String)
'Create objects.
Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection
Dim RetStr As String

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = myPattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability. It search the entire string
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(myString) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(myString) ' Execute search.

For Each objMatch In colMatches ' Iterate Matches collection.
RetStr = RetStr & "Match found at position "
RetStr = RetStr & objMatch.FirstIndex & ". Match Value is '"
RetStr = RetStr & objMatch.Value & "'." & vbCrLf
Next
Else
RetStr = "String Matching Failed"
End If
TestRegExp = RetStr
End Function

You can test your regual expression here
 An excellent introduction to regular expression can be found in this code project article

C# COM Exposed Collection

Code can be found here and here 
Visual Studio 2008 and Excel 2003-2008 required
Further details con be found on this post

Hi,
You can find attache a project that contains code that will allow you to create a C# COM exposed collection so that
1) It has Item as default property. So from VB 6.0 you can just do employees(1).Name
2) It is a IEnumerable object
3) It has a GetEnumerator() function that can be used to create VB 6.0 strongly typed collection that can be iterated using the For Each loop

 Public Function NewEnum() As IUnknown
   Set NewEnum = mNetList.GetEnumerator
End Function

Please do not to register the CLASS for COM interop just go to Project/Properties/Build Register for COM Interop.

Do not use the option Project/Properties/Application/Assembly Information/Make assembly COM Visible.
I am using the attribute  ComVisible(true) to decide which class should be visible to COM on individual basis.


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"
pause
  
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

Wednesday, September 8, 2010

How to create custom collection in VBA tricks

This solution was taken from a forum entry I found on the web



You can, but the process is a bit more manual. If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to (1) Make your Class, (2) SAVE, (3) Export the Class, (4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove") and then (5) Manually add the Attribute Lines as shown above, (6) Re-Import the edited Class.


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

Thursday, September 2, 2010

Mail Merge with multiple To, CC, distribution lists and changing Subject

Link to Advance Mail Merge.doc
Link to Advanced Mail Merge DB.xls



In this two files you will find a way to extend the MS World mail merge to send email to
1)  Have multiple mails and distribution list in the To field
2)  Have multiple mails and distribution list in the CC field
3) Have a chaning subject

Also nothe the the merged field in the .doc document can be formatted
1) To format a date, toggle the field and add \@"DD MMMM, YYY"
2) To format a number add \##,##

In the attached document you will find an example.

Sunday, July 4, 2010

Example of VBA code formatting using manoli.net

if you go to this website http://www.manoli.net/csharpformat/ you will be able in a breeze to parse your VBA/C# code in HTML format. You can see at the bottom of this blog post an example.
To make it work in blogspot, you just need to go in Design -> Edit HTML and just after


<b:skin><![CDATA[/*

insert the following code. You can find http://www.manoli.net/csharpformat/format.aspx at the bottom of the page the link to the .css style sheet.

/* CSharp VB Formatting */

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, "Courier New", Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

This is how the VBA code looks like formatte using the manoli.net application.

Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
Dim DBQ As String
Dim DefaultDir As String
Dim Connection As String

'Store the original connectin before overwriting
mOldConnection = mQry.Connection

'Build a DSN connectionless connection using OLEDB
DBQ = ThisWorkbook.FullName
DefaultDir = ThisWorkbook.Path
Connection = "ODBC;DBQ=" & DBQ & ";"
Connection = Connection & "DefaultDir=" & DefaultDir & ";"

'For Excel 2003
'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2007
Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2003 Just change the Connecton for listed query
'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
' mQry.Connection = Connection
'End If

'For Excel 2007 Just change the Connecton for listed query
If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
mQry.Connection = Connection
End If


End Sub

Saturday, July 3, 2010

Excel Tip of the Day: How to use Excel as a Relational Database Part IV

Here  you can find the Excel file JointTables II

This is the most complicated bit of this series, and before goind ahead let me just summarize what we have done so far.

We have a JoinTables II.xlsm Excel Workbook with 4 spreadsheets: Orders, Customers, ReportCustomers, ReportJoin
The Orders Spreadsheet Contains a Table TblOrders and a named Range Orders. The TblOrders use a DSN Connection NorthwindDSN to connect to the Northwind Database and download the data.
The Customers Spreadsheet contains an Excel Table TblCustomers and a named Range Customers.
The ReportCustomers and ReportOrdersAndCustomers (In the previous post I called it TblJoinOrdersAndCustormes) contain two Tables that query the JoinTable II.xlsm macro workbook.

If we need to move the position of the Access DB, all we need to do to have the worksheet work property is to go the the Control Panel and modify the property of the NorthwindDSN Connection so that it point to the new location.
However, if by any chance we move the JoinTable II.xlsm file to another location, it will not work either. This is because of the DSN Connectionless connection that we use to point to it.
I will show you now how with a bit of Advanced but simple VBA code we overcome this problem.
Of course, if we had used an Excel DSN connection to point to JoinTable II.xlsm, all we had to do, was to go to the control panel and modify the JoinTable II DSN Connection.
As you will see the solution I choose is to be preferred - you will only need to do anything in case you move the file to have it work properly.

This is the piece of code that you need to add in your ThisWorkbook class.


Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
Dim DBQ As String
Dim DefaultDir As String
Dim Connection As String

'Store the original connectin before overwriting
mOldConnection = mQry.Connection

'Build a DSN connectionless connection using OLEDB
DBQ = ThisWorkbook.FullName
DefaultDir = ThisWorkbook.Path
Connection = "ODBC;DBQ=" & DBQ & ";"
Connection = Connection & "DefaultDir=" & DefaultDir & ";"

'For Excel 2003
'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2007
Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2003 Just change the Connecton for listed query
'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
' mQry.Connection = Connection
'End If

'For Excel 2007 Just change the Connecton for listed query
If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
mQry.Connection = Connection
End If


End Sub


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim sel As Range

Set sel = Selection
On Error Resume Next
'Excel 2003
'Set mQry = sel.QueryTable

'Excel 2007
Set mQry = sel.ListObject.QueryTable

On Error GoTo 0

End Sub



I will explain it step by step

This bit here declare at class level a QueryTable Object with Events. This is because we need to access the BeforeRefresh and AfterRefresh event of this table

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String 

This piece of code just stores back the original connection string in the mQry.Connection property

Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
mQry.Connection = mOldConnection
End Sub

This event is raised each time we right click a spreadsheet. If we right click of a QueryTable object, Excel  will store the QueryTable in mQry (a class level variable) otherwise will do nothing.
 Please note a put two piece of code: one to work with Excel 2003, the other with Excel 2007


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim sel As Range

Set sel = Selection
On Error Resume Next
'Excel 2003
'Set mQry = sel.QueryTable

'Excel 2007
Set mQry = sel.ListObject.QueryTable

On Error GoTo 0

End Sub

This is the last part. Before the Refresh of the Table takes place we do
1) Save the TableQueryConnection in a class variable mOldConnection
2) We build a DSN Connectionless connection on the fly, passing it the current file path and directory
3) We update the TableQuery.Connection property with the newly created connection. We do it only for a limited list of tables. The user will have to update this list manually.

Remember that after the refresh the original connection is saved back in the TableQuery.Connection property.

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
Dim DBQ As String
Dim DefaultDir As String
Dim Connection As String

'Store the original connectin before overwriting
mOldConnection = mQry.Connection

'Build a DSN connectionless connection using OLEDB
DBQ = ThisWorkbook.FullName
DefaultDir = ThisWorkbook.Path
Connection = "ODBC;DBQ=" & DBQ & ";"
Connection = Connection & "DefaultDir=" & DefaultDir & ";"

'For Excel 2003
'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2007
Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2003 Just change the Connecton for listed query
'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
' mQry.Connection = Connection
'End If

'For Excel 2007 Just change the Connecton for listed query
If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
mQry.Connection = Connection
End If


End Sub

Excel Tip of the Day: How to use Excel as a Relational Database Part III

The Excel file can be found JoinTables II.xlsm

I will show you here how to join the Orders and Customers Table

1) Create a new Spreadsheet and call it ReportJoin
2) Data -> From Other Sources -> ExlJoinTablesNoDSN
3) Select just one field of the Customers Table
4) Go to the end of the wizard and Select "View Data in MS Query"


5) Go To Table Add...
6) Add the Orders Table
7) Remove the ID column
8) Drag a relationship line from Customers.ID to Orders.Customer ID.
9) Add individually all the fields you like form the two table
10) Click Return Data to MS Excel









Congratulation you are done!



You have just connected to MS Excel as it was a relational DB, and you have used SQL to join the two table!!!

As final step let me call this table TblJoinOrdersAndCustomers, by now you should now that you can do it by going to the Name Manager

A caveat of this approach comes from the connection to the Excel file. This connection, whether you decided to do it DSN or DSN connectionless, points directly to the location of the file, as you can see from this screen-shot in the Connectiong String text box.
So as you move your file to another location, this solution will not work anymore.



Just to be more clear:
If you have any Connection that point to a file path, as you move this file, the connection will break.
If it is a DSN connection, you just need to go to the Control Panel and change the property of this connection so that it points to the new file location.
If it is a DSN connectionless connection, you need to go the the Table External Data Property Definition Dialog box and change the connection path.
In the next post I will show you how to sort out this problem when you are connecting the Excel file without a DSN connection. I will assume that you use the current excel file as the external data source for you query, so that if you happen to move it, the file will keep working fine.

Friday, July 2, 2010

Excel Tip of the Day: How to use Excel as a Relational Database Part II

Code can be found here JoinTablesII.xlsm
We are going now to join the Orders and Customrs Tables using MSQuery.
When I try to connect to Excel as datasource, my preferred way is to use a DSN connnectionless connection.
This is how you can to do it.

1) Rename Sheet3 to ReportCustomers
2) Data -> From Other Sources -> From MS Query


 3) Choose
4) The Create New Data Source window will show up
5) Name the DataSource NorthwindNoDSN
6) Choose the MS Excel Driver




7) Click Connect...
8) Select Workbook...
9) Select the current workbook from the list
9) Click OK 4 times


ops... you will get this error


10) Click Ok, Cancel, No
to return to MS Excel?

What did go wrong?
We have two nice table in our Excel workbook but MS Query cannot see them.
Les go to Name manager and have a look at it



 As you can see the two names are table in the Name Manager Dialog box. I know that to have MS Query see the tables I need to have Named Range (not Named Tables). So, let's try this out


1) Click New
2) Enter Customers as Name
3) And =TlbCustomers in the formula bar





Now we have a name range that point to the TblCustomers. Let's try if it works.

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN

You Still Get the same Error!!!!
We are just out of luck. This is something that Microsoft should fix in one of their future release!
Let try another solution.
It seems that pointing directly or indirectly to TblCustomers does not fix the problem.
So let create a Name that points to the directly to the range.

1) Formulas -> Name Manager -> Customers
2) Edit..
3) Then Make sure that the formulas looks like =Customres!$A$5:$F$34 and not like =TblCustomers[#All]



With this new name range try

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN



We Finally got it!!!
3) Select all the fields
4) Go to the End of the Wizard, Select Cell A5 in the ReportCustomers worksheet.

You have finally managed to query an Excel Table using MS Query.
As you can see from this screen shot, you have just created a ODBC Connectionless  connection to the JointTablesII excel file.






Rename the new Query to ReportCustomers going to the Name Manager as described above.

The Customers name range have these properties:

1) Will change automatically in size as we add new Customers
2) Will change automatically in size as we add column to the left in the Table
3) Will not work properly if we add a column to the right at the end of the table.

This is why I wished that Customers = TblCustomres[#All] would have worked.
So just keep an eye not to add columns to the end of the range.

The Orders name range have these properties:

1) Will change automatically in size as we add new Orders


So any time we change the columns from the underlying query that retrieve the order we will need to redefine it.

The fact the the two named range are not sensitive to new column in not a major annoyance.
Usually when a table is set up, the thing that is most likely to change is the row count not the column numbers.

In the next post you will see how to join the two tables and how to sort out some other minor annoyance.

Excel Tip of the Day: How to use Excel as a Relational Database Part I

Here you can find the Excel Workbook for the tutorial JoinTable.xlms

I am going to show you how to use Excel as a Relational database. This is quite an advanced topic, so I will assume that a user is already familiar with Excel 2007 table, names as pointer to functions, VBA and how to access to external data source. I will cover each of this topic in details in my post, but I will assumer them as known in this one in order not to loose focus and keep the post shorter.
What you need: Excel 2008, Access 2007 and the Northwind database sample.
In this post I will show you how to connect to an Excel Spreadsheet and query it with MS Query, so I will show you how to deal with Excel as it was a relation data source.
The interesting part is that I will show you how to query an Excel Workbook form itself.

Create the Workbook and DSN Connnection

1) Create a new Workbook and name it JoinTables, save it as .xlms, a Macro enabled spreadsheet

2) Create a DSN connection to the Northwind data base (see here ) as name it NorthwindDSN


 Create the Orders Query

1) Rename Sheet1 to Orders

2) Go to Data -> From Other Sources -> From MS Query

3) Select the NorthwindDSN Source

4) Go ahead and import the Orders Table in Orders Sheet cell A5.
    Select the following fields: Order ID, Custormer ID, Order Date, Shipped Date, Ship Name, Ship Address, Ship City, Ship State/Provicne, Ship Zip/Postal Code, Ship Country/Region, Payment Type



This is what you should end up with in your Orders Spreadsheet



We need now to change this Table Name "Table_Query_From_NorthwindDSN" in a more meaningful name.
1) Go to Formula -> Name Manager
2) Select Table_Query_From_NorthwindDSN
3) Click Edit
4) Change it name to TblOrders
5) Click Ok, Close

You have now a Name called TblOrders that we can use to refer to the Table Orders.


Create the Customer Query


1) Rename Sheet2 to Customer

4) Go to Data -> From Other Sources -> From MS Query

5) Select the NorthwindDSN Source

6) Go ahead and import the Customer Table in Customers Sheet cell A5.
    Select the following fields:ID, Company, Last Name, First Name, Email Address, Business Phone


We have now two table connected with each other with the custormer ID field.
The customer ID field act as primary key in the customer table, and as foreign key in the Orders Table.
Our target is to join the two table together using MS query.
Before going ahead I would like to point out tha we can have 3 different case

1) Orders and Customers Table come form a Data Base

2) Order il a Spreadsheet Table, Customers is a DB Table (and viceversa)

3) Orders and Customer are both Excel Table

To see if there is any difference in the solution I suggest we are going to trasform the Customer Table in a local SpreadSheet Table.

1) Right click on the Customers  Table
2) Table -> Convert To Range -> OK

This way we do not have any more the Customer Table connected to the Northwind DB.

3) Click on the Table
4) Insert ->  Table -> Ok

We have name a spread sheet Table that will be called Table3. Again let's change it to a better name

1) Go to Formula -> Name Manager
2) Select Table3

3) Click Edit
4) Change it name to TblCustomers
5) Click Ok, Close

 If you go to the Names Combo box, you will see that we have tow Names: TblCustomers, TblOrders with workbook scope.
If you are using Excel 2003, it is worth point out that
1) The table Orders will have a scope limited to the worksheet, so to make it global you will have to create another name that points to it
2) The equivalent in Excel 2003 of a Table is called "List", so you need to create a List, rather than insert a table

I will cover both of this two topics in more details in my later post.
It is time now to move to part two.

Thursday, July 1, 2010

Excel Tip of the Day: How to connect to an external Data source Part II

In this blog post I will show you how to create an DSN Connectionless connection to Access.
First of All you need to go to
Data -> From Other Sources -> From MS Query



On the Choose Data Source Dialog Box, choose



On the Create New Data Source
Insert a Name for the connection and select the appropriate driver as shown in the picture.
Then Click Connect and Select the Northwind MS Access file.
Clik OK twice.
Select a Table with few fields, go to the end of the wizard, select a cell and you are done.




If you right click on this Table, go to
Table -> External Data Properties -> Click the Property button close to the name text box and click definition
you will see this window


As you see from the Connection String Text Box, there is no "DSN=" string.
Congratulation, you have just created an DSN connectionless connection.
Some of you might have noticed that Excel created a "Query form Northwind1" connection.
The Northwind1 stems from the fact that I had already a Northwind DSN connection with the same same, in the same Excel file.
I would suggest to name NorthwindDSN the connection with a DSN datasource to distinguish it from the one that connects to the same data base without DSN.

Excel Tip of the Day: How to connect to an external Data source Part I

You can connect Excel to an external data source using a DSN Connection or using a DSN connectionless string.
I will first show you how to connect to a DSN Connection for an Access database
First of all you need to create a DSN Connection. To do that go to

Control Panel  -> Administrative tools,-> Data Sources (ODBC)
The ODBC Data Source Administator Window will pop up.


Then click the Add.. button and the following window will show up (for W7 64bit see here )



Select the drive you need. For this example I am going to connect to the North wind Database.
Scroll down and select the  Microsoft Access Driver. I am picking the second one in the list because I need to connect to an Access 2007 DB.





Click Finish.
This window will show up




Insert the ODBC Data source name and a description for it.
Click Select... and browse to the .accdb/.mdb file that you need to connect to.
You are done with the ODBC connection.

Go to Excel and click and go to

Data -> From Other Sources -> From MS Query,





This action will display the Choose Data Source window.
Select Northwind and click Ok.
Select the Employee Table and few ot its fields
Click Next 3 times
Click Finish
Click OK









You are done!
if you right click on the Table and Select Table -> External Data Properties,
The External Data Property Window will show up.
If you click the property button, which is on the righ side of the Name Text box you will see the Connection Property screen.
Click on the Definition Tab.
If you have a look at the Connection String Text Box, there is "DNS=Northwind", this means that the Query is using a DSN Connection to connect to Northwind





In the Next Tutorial, I will show you how to create DSN Connection less connections.
Personally I normally use DSN Connection when I am connecting to External Data Source (Access, SQL Server) from Excel, DSN Connectionless when I am connecting to Excel as an external data source (This is an advanced topic I will cover in a future post) and the JET.OLDE.DB provider when connecting using VBA (This will be part of another post).

As usual please leave a message if you like the post or have any comment.