Hello again and Welcome to the "On-Error-Resume-Next-prevented-me-from-
seeing-the-problem"-club ;-)
In your code, the following lines are problematic:
[...]
recCount = rst.RecordCount
xlWs.Cells(1 + recCount, fldCount).Select
[...]
The problem is that you open your recordset without specifying the
cursor-type. By default, ADO generates a ForwardOnly cursor which
is not able to reflect the record count in the RecordCount-property
of the Recordset. The Online-Help says:
"The cursor type of the Recordset object affects whether the number
of records can be determined. The RecordCount property will return -1
for a forward-only cursor; the actual count for a static or keyset cursor;
and either -1 or the actual count for a dynamic cursor, depending on the
data source."
Now back to the problem: In your case this means, that everything
runs fine until the following line is reached:
recCount = rst.RecordCount
After this line has been executed, your variable recCount contains
the value -1 (you can verify this using a breakpoint and/or step
by step execution). In the next line, you try to do the following:
xlWs.Cells(1 + recCount, fldCount).Select
Now, since recCount is -1, the statement is evaluated as:
xlWs.Cells(1 + (-1), 3).Select
... which can be simplified to:
xlWs.Cells(0,3).Select.
However, the cells-property works 1-based, so this statement
generates an error. Problem is, you turned off error handling
using On Error Resume Next, so you didn't notice and the macro
ran on...
A solution to your problem is as simple as adding the kind of
cursor-type which supports accessing the actual record count
through the RecordCount property. Therefore, simply add the
predefined constant adOpenKeyset to the line where you open
the recordset:
rst.Open "...", cnt, adOpenKeyset
Problem solved, everything fine? For the time being: yes. But
obviously, just turning of Error handling and then forgetting about
it is not a good idea. Instead, you might want to turn Error handling
back on after the critical lines of code (getting access to Excel):
On Error Goto 0
Cheers,
Martin
Forrest said:
Tushar & Martin, thank-you for your continued interest. Below is the
entire code up to my problem point. What does not work is the variables
(recCount, fldCount) are not recognized, thus leaving the cursor in cell A1
on my newly created spreadsheet. My goal without using the End property is
to count the number of records in eachrecord set and use this for the range
offset to run the XIRR function against columns A (dates) and C (amounts). I
have 200 or so unique records that I need to filter through this process.
Forrest
Private Sub Command0_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object ' Excel application
Dim xlWb As Object ' Excel workbook
Dim xlWs As Object ' Excel worksheet
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer 'Index variable for the current column
Dim iRow As Integer 'Index variable for the current row
'Set the string to the path of your Database
strDB = "c:\Forrest\FundSectors2002home.mdb"
'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
'Open recordset based on CashFlow1 table
rst.Open "Select CashFlow1.Date, CashFlow1.Transaction, CashFlow1.Amount From CashFlow1", cnt
'Open or create an instance of Excel and add a workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Activate current Excel object
If Err.Number <> 0 Then
Err.Clear ' Clear Err object in case error occurred.
Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel
End If
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
'Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True
'Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
'Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
recCount = rst.RecordCount
xlWs.Cells(1 + recCount, fldCount).Select
