This code needs to be run on an open worksheet, so should be placed between "Part A" and "Part B" from the previous post. It is probably best to use B(1) for now, so that you can see what is going on more easily.
a) using Cells, Range (and UsedRange)
There are a few methods of working with the data depending on what you want to achieve.
The simplest method is to read/write a single cell, which can be done using the Cells collection of the WorkSheet like this:
VB Code:
Dim my_variable As String
my_variable = oXLSheet.Cells(2, 1).Value
This copies the text in the cell A2 (specified in the brackets: row 2, column 1) to the variable "my_variable".
To write a single cell you just need to change the order of the expression like this:
VB Code:
Dim my_variable As String
my_variable = "hello"
oXLSheet.Cells(2, 1).Value = my_variable
'or just this:
oXLSheet.Cells(2, 1).Value = "hello"
To read or write the Formula of the cell rather than the displayed text, just change the ".Value" to ".Formula", e.g.:
VB Code:
oXLSheet.Cells(3, 1).Formula = "=A1+2"
All of the methods so far can also be used with the Range object instead of Cells, which allows you to specify the cell in the standard Excel "A1" address format, e.g.:
VB Code:
oXLSheet.Range("B4").Value = "range test"
There is an advantage to using the Range object, in that you can work with multiple cells at the same time, for example you could set the value of all cells in the range D3:E5 to a single value like this:
VB Code:
oXLSheet.Range("D3:E5").Value = 3
This may seem a little pointless, but with a little alteration you can see the power of this, as you can set all the values differently by using an array like this:
VB Code:
Dim lMyArray(2, 1) As Long
lMyArray(0, 0) = 1
lMyArray(0, 1) = 2
lMyArray(1, 0) = 3
lMyArray(1, 1) = 4
lMyArray(2, 0) = 5
lMyArray(2, 1) = 6
oXLSheet.Range("D7:E9").Value = lMyArray
The location of the cells within the range can be deduced from the array positions in the same way as for the Cells collection, i.e.: (row, column).
Unfortunately you can't quite do this the other way around (i.e.: you can't set lMyArray equal to the range), as VB will not allow you to assign to an array. Instead you need to use a Variant to hold the array instead, e.g.:
VB Code:
Dim vArray As Variant
vArray = oXLSheet.Range("D7:E9").Value
You can copy the entire used range of the spreadsheet simply using a special built-in range called UsedRange, like this:
VB Code:
Dim vArray As Variant
vArray = oXLSheet.UsedRange.Value
'vArray now contains the values of ALL the cells in used range of the worksheet.
'example usage of the array:
Dim lngCol as Long, lngRow as Long
For lngRow = 1 To UBound(vArray,1)
For lngCol = 1 To UBound(vArray,2)
MsgBox vArray(lngRow, lngCol)
Next lngCol
Next lngRow
Setting values in the spreadsheet using an array has the advantage of being far quicker, as each time you enter data Excel does some work like re-calculating formulas. Using an array means that this extra work is only done once for the entire array, rather than once per cell.
b) using a RecordSet
There is one more way of putting data into Excel that is extremely useful, which is to copy data directly from a recordset that you have gotten from a database. You simply say which is the first cell to put the data into, and Excel works out the rest of the cells that are required (although you can specify the maximum rows/columns to use in two optional parameters).
I have not created the recordset in these examples, as there are many ways in which you can do it. If you need help with this, please see the tutorials (or post a new thread) in the Database Development forum on this site.
VB Code:
'create and fill a recordset here, called oRecordset
oXLSheet.Range("B15").CopyFromRecordset oRecordset
Note that you can also fill in the field names using an array like this:
VB Code:
Dim iCount As Integer
With oXLSheet 'Fill with data
For iCount = 0 To (oRecordset.Fields.Count -1)
.Cells(1, iCount+ 1) = oRecordset.Fields(iCount).Name
Next iCount
.Range("A2").CopyFromRecordset oRecordset
End With
"But I thought setting individual Cells was slow?" It is, but to use Range you need to know the target cells, in order to do that you need to know the column address of "B" + oRecordset.Fields.Count, which requires a bit more work. Luckily I have provided a function in section 13 "Useful functions and downloads" called xl_Col, which returns the column name for the column number that you specify (there is also xl_ColNo, which converts the name back into a number). This function allows you to do use the Range object like this:
VB Code:
Dim iCount As Integer
Dim sFieldNames() As String
ReDim sFieldNames(oRecordset.Fields.Count - 1) As String
For iCount = 0 To (oRecordset.Fields.Count -1)
sFieldNames(iCount) = oRecordset.Fields(iCount).Name
Next iCount
oXLSheet.Range("A1:" & xl_Col (1 + oRecordset.Fields.Count) & "1")
.
