Activate
Me.DataRegionStart.Activate
Selection.CurrentRegion.Select
Selection.ClearContents
End Sub
Add one last function to do our autofit cell formatting, and clean up the worksheet:
Public Sub DoAutoFit()
Me.Worksheet.Select
Me.DataRegionStart.Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Me.InitialCellSelection.Select
End Sub
By now, I??™m sure you??™ve noticed that this code is very similar to the original code in our
standard module. The major difference is that rather than referring to specific cells, we are
using internal class properties such as Me.DataRegionStart.Select.
That??™s all there is to our cExcelSetup class. Let??™s create our cData class to populate our
worksheet with data. Add the following private module-level variables:
Private m_cnn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_sConnString As String
Private m_sSQL As String
CHAPTER 2 n DATA IN, DATA OUT 90
These are the same tools we??™ve been using all along to connect to our data and return sets
of data from the AdventureWorks database. We??™re going to create properties to hold our connection
string and SQL statement. We??™ll also create methods (functions) to open and close our
ADO connections and recordsets.
Add the following Property Get/Lets:
Public Property Get ConnectString() As String
ConnectString = m_sConnString
End Property
Public Property Let ConnectString(newString As String)
m_sConnString = newString
End Property
Public Property Get SQL() As String
SQL = m_sSQL
End Property
Public Property Let SQL(newSQL As String)
m_sSQL = newSQL
End Property
Next we are going to add methods to open and close our ADO Connection object:
Function OpenConnection()
If m_sConnString <> "" Then
m_cnn.
Pages:
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115