Pro-business Excel VBA Programming

Situation:
In a few days from now, I will be doing a new Excel VBA project for a more-than-2-year-old client. The new project will require me to produce pivot-table reports from an external data source. This kind of requirement is usually easy because Excel PivotTable already supports external source of data. But the problem with this project, they are requiring me to come up with an Excel VBA program that be used to connect with different database platform. As I was thinking about the solution of this problem, I’ve posted to different Excel VBA forum to seek for help. No one has given me the complete answer but while I was reviewing their suggestions, I tried to put them together and came up with the correct solution. Truly forums are very helpful. Below is the sample source code of the database connection string builder that can provide different OLEDB connectivity programmatically:


Solution:

'PROCEDURE TO GET CONNECTION STRING USING DataLinks 
Function GetConnectionString() As String
   Dim objLink As New MSDASC.DataLinks
   Dim strConnectionString As String
   strConnectionString = ""
   On Error GoTo LinkErr
   strConnectionString = objLink.PromptNew
LinkErr:
   GetConnectionString = strConnectionString
End Function

'PROCEDURE TO TEST THE FUNCTION ABOVE
Sub Test() 
   Dim strCN As String
   strCN = GetConnectionString
   If strCN <> "" Then 
      MsgBox strCN
   End If
End Sub

The heart of the code is the GetConnectionString procedure. It uses the MSDASC.DataLinks object. But before you can use this code you need to load 2 object files from Microsoft. They are:

1.) Microsoft ActiveX Data Objects 2.5 Library (or later version)
2.) Microsoft OLE DB Service Component 1.0 Type Library

See: Referencing Objects

When MSDASC.DataLinks generates a connection string, it stores the value in strConnectionString variable and eventually, it is the value to be returned by the GetConnectionString function.