Excel VBA Copy the contents of a recordset to a range

To copy the results of query, you first have to execute a query - using either ADO or DAO (for this example, ADO will be used). Then, you use the CopyRecordset method of the range object

Excel VBA

Dim objConn as ADODB.Connection

Dim objCmd as ADODB.Command

Set objConn = Application.CurrentProject.Connection
Set objCmd = New ADODB.Command
With objCmd
    Set .ActiveConnection = objConn
    .CommandType = adCmdStoredProc
    .CommandText = "qryBrazilianRevenueExtract"
    Set rs = .Execute
End With


Set objRange=Worksheets(1).Cells(2,1).CurrentRegion
objRange.Cells(1, 1).CopyFromRecordset rs

Excel VBA Removing the top row from a range

Every need a quick way to excise the header row from a range. Well this snippet will work with any range that has header.  The trick is to use the OFFSET() method of the range object.

Dim wksTemplate As Worksheet
Dim objRange As Range
Dim lngRowCount As Long

Set wksTemplate = ActiveWorkbook.Worksheets("Data")

Set objRange = wksTemplate.Cells(1, 1).CurrentRegion

        
lngRowCount = objRange.Rows.Count
Debug.Print "Before resizing " & lngRowCount
Set objRange = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count)
lngRowCount = objRange.Rows.Count
Debug.Print "After resizing " & lngRowCount

Tips, Tricks and Traps (More) Excel.Application & Excel.Range

Questions

1.       How to stop the screen from flickering during a long-running program?

2.       What statement requires that the programmer explicitly type each variable?

3.       What method of the range object can be used to extend the number of columns in a range from 3 to 6?

4.       What property of the range object would put 5 in cell $A$1?

5.       What function displays both a message and icons like the one in Figure 1 to the user?

   Figure 1

  

6.       What function determines whether a cell is populated with data?

7.       How to set the default indexing of an array to start at 1?

8.       What method of the Application prevents recursively calling an event’s code written for a Sheets, Workbook or Application object's event?

9.       What property of the Application object suppresses the warning message that Excel display’s when certain action- like OverWriting a file are performed

10.   What is the method of the range object that will select all the cells from A1 to C3?

11.   Bonus: If data has been copied to the clipboard, what method of the range object would copy it’s contents to a new range?
 

 

Answer Key

1.       Application.ScreenUpdating =False

2.       Option Explicit

3.       Resize method

4.       Value property

5.       MessageBox function

6.       IsEmpty()

7.       Option Base 1

8.       Application.EnableEvents=False

9.       Application.DisplayAlerts property=False

10.       Range(“A1:C3”).Select

11.       Range.PasteSpecial

Excel VBA Range Cells method

This Cells method provides another way to refer to ranges and is best used by referring to rows and columns by numbers in the Excel VBA code.  The Cells method comes in 2 flavors:  single cell reference and multi-cell references.

Tips:

·         The row offset value can NOT be zero

·         The column offset value can NOT be zero

·         The offset values (arguments) are 1-based

·         If you make an invalid range address, the Excel VBA error message:  Run-time error ‘1004’:  Application-defined or object -defined error

·         The Range keyword is needed for the multi-cell version

How to use the Cells method

Single cell reference for Cells method

Ex
1	Activesheet.Cells(1,1).Value="Hello"	Range on current worksheet
2	ActiveWorkbook.Worksheets(2).Cells(3,1).value="Test"	Use a range in another worksheet but in the same workbook
3	? Workbooks("Log of type of blog posts.xlsx").Worksheets(1).Cells(1,1).value	Select a range in another workbook

Multi-cell reference for Cells method

You can also refer to a range by specifying the upper left (first argument) and the lower right last argument) cells' arguments

ActiveSheet.Range(ActiveSheet.Cells(3, 1), ActiveSheet.Cells(4, 1))

Another cute way is to have two range variable define the boundaries of the Cells method.  For example, the following code is very transparent as two different range variable define the boundaries of the Cells method. 

Dim objTopLeftCell As Range

Dim objBottomRightCell As Range

Dim objRange As Range

'Step 1: instantiate object variables

Set objTopLeftCell = ActiveSheet.Cells(1, 1)

Set objBottomRightCell = ActiveSheet.Cells(5, 5)

'step 2: create a range using the Cells method

Set objRange = ActiveSheet.Range(objTopLeftCell, objBottomRightCell)

objRange.Select

See also

Cells method on MSDN

Excel Range Tips, Tricks and Traps

 
1. How to refer to a range using A1 notation
Range("A1:A5").Copy Range("B1")  2. How to put a value in a range(cell)
Range("C1").Value="Hello world"  3. How to create a multi-cell range
Range("A1:G5").Select  4. How to refer to a multi-cell range in a particular worksheet
Worksheets(1).Range("A1,A5,B4:B8").Select  5. How to refer to the currently active cell
? ActiveCell.Address  6. How to find the parent object (worksheet) of a range object
? ActiveCell.Parent.Name  7. How to refer to a range in the workbook that currently has focus 
              ActiveWorkbook.Worksheets(1).Range("A1:C2").Select 

8. How to refer to a range In the workbook that contains the code
ThisWorkbook.Worksheets(1).Range("C2").Select  9. How to add a formula to a range Range("B4").Formula="=45*10"  10. How to determine if a cell has a formula
? ActiveCell.HasFormula

(More) Tips, Tricks and Traps for the Excel.Range

 

1.       How to determine the address of a  range

? ActiveCell.Address()

 

2.       How to remove  formatting  in a range(cell)

ActiveCell.ClearFormats

 

3.       How to create a union of ranges(merge cells)

Range("A1:G5").Select

 

4.       How to select an entire column of range

Range("A1:D5").Columns(2).Select

 

5.       How to select an entire row of range

Range("A1:D5").Rows(3).Select

 

6.       How to test if a cell is Not populated with data

?  IsEmpty(ActiveCell.Value)

 

7.       How to test to see if cell is populated with a valid date

?  IsDate(ActiveCell.Value)

                               

8.       How to determine if a cell contains number(s)

? IsNumeric("hello")

 

9.       How to  copy the content of  a cell to another range

Activecell.Copy Destination:=ActiveCell.Offset(2,1)

 

10.   How to perform a PasteSpecial  operation from the contents copied to the Clipboard (via Control+C)

Activecell.Copy

ActiveCell.Offset(4,1).PasteSpecial(xlPasteAll)