Microsoft Office Tutorials and References
In Depth Information
19.3.16 CopyFromRecordset Method
19.3.16 CopyFromRecordset Method
For those readers familiar with DAO, CopyFromRecordset is a very powerful method that copies
the contents of a DAO Recordset object onto a worksheet, beginning at the upper-left corner of the
specified range. Note that if the Recordset object contains fields with OLE objects in them, this
method fails.
To illustrate, consider the following code, which requires that a reference to Microsoft DAO is set
in the References dialog box in the Excel VBA Tools menu:
Dim rs As Recordset
Set rs = _
DBEngine.OpenDatabase("d:\excel\excel.mdb"). _
OpenRecordset("Objects")
Range("A1").CopyFromRecordset(rs, 10, 10)
This code opens an Access database named d:\excel\excel.mdb , creates a recordset based on the
table named Objects , and then copies the first 10 columns of the first 10 rows of the recordset to
the current worksheet, starting at cell A1.
Note that, in general, copying begins at the current row of the Recordset object (which in our
example is the first row, since we opened the recordset anew).
19.3.17 CreateNames Method
This method creates range names based on text labels in specified cells. The syntax is:
RangeObject .CreateNames( Top , Left , Bottom , Right )
The parameters are optional and have the default value of False . If one of the parameters is set to
True , then the corresponding row ( Top or Bottom ) or column ( Left or Right ) is used to
supply the names for the named ranges. If all of the parameters are False , then Excel tries to
guess the location of the names. (I would generally advise against letting an application guess at
anything.)
To illustrate, the following code, when applied to the sheet in Figure 19-8 , will define three named
ranges:
Range("A1:C3").CreateNames Top:=True
For instance, the range A2:A3 will be named John.
19.3.18 CurrentRegion Property
This useful property returns a Range object that represents the current region, which is the region
bound by the closest empty rows and columns. To illustrate, the following code, when applied to
the sheet in Figure 19-11 , selects the rectangular region A2:C4:
ActiveCell.CurrentRegion.Select
Figure 19-11. Illustrating CurrentRegion
 
Search JabSto ::




Custom Search