asked on

Write to Excel97 using DAO

I need to write a series of values to a Excel worksheet. The problem that I am having is how to refer to the cells where I want to write the data. I want to start on page PC in column 10 (K) row 2 and write to successive rows.

Here is what I have so far:

Set Db = DBEngine(0).OpenDatabase(App.Path & "\" & "O_Rate.xls", False,
False, "Excel 8.0; HDR=1")
Set Rs = Db.OpenRecordset("PC$")

If Rs.RecordCount > 0 Then
Rs.MoveLast 'size the array
Rs.MoveFirst
End If
Debug.Print Rs(0) 'this works, I get the
correct value for A,2

With Rs
For i = 1 To 33
Debug.Print i; " * "; lngAVinTotal(i)
.Edit
![K,1+i] = lngAVinTotal(i) 'this is the problem, how
do I refer to the cell?
.Update
Next i
.Close
End With
Db.Close

I get a Run-time error '3265' Item not found in this collection.

ThanksInAdvance!!!

aeklund

Try this...

With rs
Dim i As Integer
For i = 1 To 33
Debug.Print i; " * "; lngAVinTotal(i)
.Edit
rs(10) = lngAVinTotal(i)
.Update
.MoveNext
Next i
.Close
End With

lewisg

ASKER

Not good...

The line "rs(10) = lngAVinTotal(i)" returns a "Run-time error '3265' Item not found in this collection" error.

This works:

Set Db = DBEngine(0).OpenDatabase(App.Path & "\" & "Rate.xls", False, False, "Excel 8.0;")
Set Rs = Db.OpenRecordset("select * from [PC$K1:K34]")

For i = 0 To 32
Rs.Edit
Rs.Fields(0).Value = curAVinTotal(i + 1)
Rs.Update
Rs.MoveNext
Next i

aeklund

Glad to hear that you found a solution.

DanRollins

lewisg@lc, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer

ASKER CERTIFIED SOLUTION

SpideyMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial