Now that we've created the objects we need, let's put them to use in client code.
In the VBE, add a new standard module and name it basManagers. Add two module-level variables to hold our cExcelSetup and cData objects:
Dim m_cData As cData Dim m_cXL As cExcelSetup
These are placed at module level in case we need to use the objects across function calls. Create a new subroutine and name it GetManagers. Add the following code:
Dim sConnString As String Dim sSQL As String
Set m_cXL = New cExcelSetup Set m_cData = New cData sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;"
& "Database=AdventureWorks;Trusted_Connection=yes;" sSQL = "SELECT HumanResources.Employee.EmployeelD, Person.Contact.FirstName,"
Person.Contact.LastName FROM Person.Contact" * INNER JOIN HumanResources.Employee" *
ON Person.Contact.ContactID = HumanResources.Employee.ContactID" * WHERE (((HumanResources.Employee.EmployeeID) In" * (SELECT HumanResources.Employee.ManagerID" * FROM HumanResources.Employee)));"
Here we are instantiating our cExcelSetup and cData objects, and preparing variables to set up the cData class.
When we analyzed the original code, we found we needed to have three sets of functionality, prepare the worksheet for data import, get and display the data, and resize the columns for the data. We are going to create helper functions to do most of this work.
Add a new subroutine to basManagers and name it DoClearSheet(). Add the following code:
Set .Worksheet = Sheets("Sheetl")
.SetKeyCells .Worksheet.Range("Al"), .Worksheet.Range("A3") .SetupWorksheet
Note that we're using our cExcelSetup object's SetKeyCells method, allowing us to assign values to the InitialCellSelection and DataRegionStart properties with one line of code.
Add another subroutine called GetData. This procedure will take two arguments: the connection string and the SQL statement. Here is the code for the GetData method:
Sub GetData(ConnString As String, which As String)
.ConnectString = ConnString
.SQL = which m_cXL.Worksheet.Range("A1").CopyFromRecordset .GetData .CloseConnection End With End Sub
Both of these methods use only our cExcelSetup and cData objects with no external code. Let's finish our GetManagers procedure by adding calls to these methods, and also adding some cleanup code. The entire GetManagers subroutine should look like this:
Dim sConnString As String
Set m_cXL = New cExcelSetup Set m_cData = New cData sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;" sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName," * & " Person.Contact.LastName FROM Person.Contact" * & " INNER JOIN HumanResources.Employee" *
& " ON Person.Contact.ContactID = HumanResources.Employee.ContactID" * & " WHERE (((HumanResources.Employee.EmployeeID) In" * & " (SELECT HumanResources.Employee.ManagerID" * & " FROM HumanResources.Employee)));" DoClearSheet
GetData sConnString, sSQL m_cXL.DoAutoFit Set m_cData = Nothing Set m_cXL = Nothing End Sub
I mentioned a moment ago that neither of our helper methods made any direct VBA calls. The same is true of the GetManagers method. All of our work is being done by our objects from start to finish. The beauty of this is that we can drop these classes in any Excel project and have this functionality available instantly.
In our current Excel project, we can change the database and/or SQL statement and import any data we need via the cData object.
Was this article helpful?