MS Excel VBA - issue with setting ADODB.Connection inside the class

Question

I am building MS Excel tool which imports the data from other workbooks using ADODB.Connection. It works fine when I build the solution in regular modules but as I am using this method quite frequently I wanted to build a class module which I can use in other automations. But as I am not flexible with VBA classes I struggle with overcoming one problem.

Here is the code from the regular module:

Option Explicit
Dim clsSQL As clsWbkSQLImport
Dim strConnString As String
Private Sub btnImportData_Click()

strConnString = Me.Range("RawDataPath")

Set clsSQL = New clsWbkSQLImport
clsSQL.ConnProvider = "Microsoft.ACE.OLEDB.12.0"
clsSQL.ConnString = "Data Source=" & strConnString & "; Extended Properties='Excel 12.0; HDR=YES'"
clsSQL.ConnProperties = "Excel 12.0; HDR=YES"
clsSQL.SetConnection

End Sub

And here is my class module:

Option Explicit
Private strProvider As String
Private strConn As String
Private strProperties As String
Private con As ADODB.Connection
Property Let ConnProvider(strCP As String)
strProvider = strCP
End Property
Property Let ConnString(strCS As String)
strConn = strCS
End Property
Property Let ConnProperties(strCPP As String)
strProperties = strCPP
End Property
Property Set ConnSet(cn As ADODB.Connection)

With cn
    .Provider = strProvider
    .ConnectionString = "Data Source=" & strConn & "; Extended Properties='" & strProperties & "'"
    .CursorLocation = adUseClient
    .Open
End With

Set con = cn

End Property
Sub SetConnection()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

If strProvider = "" Or strConn = "" Or strProperties = "" Then
    MsgBox "Connection parameters were not provided."
    Exit Sub
Else
    Set con = Me.ConnSet(cn)
End If

End Sub

The purpose is to transfer connection string variables to respective properties and then establish a connection. I am getting an error in this line and I guess it is not written in a correct way.

Set con = Me.ConnSet(cn)

Can you give me some hints how this class should look like?

Many thanks!

Answer 1

There are a couple of issues with your code.

1. Properties in classes have getters and setters. In VBA the syntax is Get for the getter and Let for the setter. The Public property set line won't do anything. Edit: In VBA the set keyword is used to assign objects to variables of course, but than again, that's not what we're talking about here.

2. Classes can have methods: Small subroutines that do something within the class. For this you can use Private sub for routines that are only available to the class itself and Public sub for routines that need to be accessible from other modules. This is what you need instead of the afforementioned property set.

3. The line clsSQL.ConnString = "Data Source=" & strConnString & "; Extended Properties='Excel 12.0; HDR=YES'" is parsed into "Data Source =" etc. so this will lead to an incorrect connection string that starts with "Data Source=Data Source=..."

Other than that: You should have a very clear idea of what the class should do and what you want to do outside of it. E.g. what should be the reusable part. If I read your code correctly, you're trying to come up with a class that has 3 properties (string for data provider, string that points to file or db to connect to and string properties). Next the class should combine these things into a proper connectionstring and provide you with a connection object.

The entire class would then become:

Option Explicit
Private p_strProvider As String
Private p_strConn As String
Private p_strProperties As String
Private p_con As ADODB.Connection

Private Sub Class_Initialize() 'Use initialization to make sure there's Always the private connection object
    Set p_con = new ADODB.Connection
End Sub
Private Sub Class_Terminate() 'Clean up after yourself
    Set p_con = Nothing
End Sub

'Properties needed:
Property Let ConnProvider(strCP As String)
    p_strProvider = strCP
End Property
Property Let ConnString(strCS As String)
    p_strConn = strCS
End Property
Property Let ConnProperties(strCPP As String)
    p_strProperties = strCPP
End Property

Private Sub OpenConnection()
'Takes the variables, builds a connectionstring, creates the connection
    Dim conStr As String
    If p_strProvider = "" Or p_strConn = "" Or p_strProperties = "" Then
        MsgBox "Connection parameters were not provided."
        Exit Sub
    Else
        conStr = "Data Source=" & strConn & "; Extended Properties='" & strProperties & "'"
        With p_con
            .Provider = strProvider
            .ConnectionString = conStr
            .CursorLocation = adUseClient
            .Open
        End With
    End If
End Sub
Public Function GetConnectionObject() As ADODB.Connection
'Builds and then exposes the connection object from within the class to the outside world
    OpenConnection
    Set GetConnectionObject = p_con
End Function

This class can be used in a module as follows:

Option Explicit Dim clsSQL As clsWbkSQLImport Dim strConnString As String

Sub Test()
Dim clsSQL as clsWbkSQLImport
Dim connectionObject as ADODB.Connection
strConnString = Sheets("somesheet").Range("RawDataPath") 'I take it this named range holds a filepath?

Set clsSQL = New clsWbkSQLImport

clsSQL.ConnProvider = "Microsoft.ACE.OLEDB.12.0"
clsSQL.ConnString = strConnString
clsSQL.ConnProperties = "Excel 12.0; HDR=YES"

Set connectionObject = clsSQL.GetConnectionObject
'connectionObject will now hold an open ADODB connection.
End Sub

Some more error handling is advisable. Depending on what you attempt to do keep time-outs etc. into mind. Other than that: Indeed check out Chip Pearson's website on classes :)

Answer 2

A Set property does not return anything. It is like a sub which runs when you use a statement like this: Set Me.ConnSet = cn

This will pass 'cn' as a argument to your Property Set ConnSet(cn As ADODB.Connection)

Here is some more on Class Modules including Set, Let, and Get properties: Classes in VBA: Chip Pearson