Access and Excel ( Visual Basic )

[bsunuwar]

Hi Everybody,

I have been trying to solve this problem for long time but I am not able to find any solution.We use some access forms and excel spreadesheet to maintain,update the cheques we print for our daily expenses.When I press update button on the access form it should automatically update the excel spreadesheet with new cheque number description,date,amount e.t.c which I fill on the access form.It was working fine but since couple of months it only updates the same row in excel spreadesheet instead of moving to next row it only update same row again and again.I am not good at access Visual basic so cant do anything without a help. Please look at my codes and see if there are any problems.

Private Sub Command16_Click()

Dim xl
'Dim opendialog As New MSComDlg.CommonDialog
Dim i
Dim i2
Dim cell
Dim rowtouse
Dim qry
Dim rs
Dim intRecCount
Dim db As Database

Set db = CurrentDb()

'opendialog.Filter = "xls"
'opendialog.Filename = "*.xls"
'opendialog.ShowOpen

Set xl = CreateObject("Excel.Application")
'xl.Workbooks.Open (opendialog.File)
xl.Workbooks.Open ("\\sv1\Finance\Management Accounts\Accounts 01.04.09-31.03.10.xls")
xl.Sheets("Cheques Written").Select

For i = 3 To 16000
cell = "C" + CStr(i)
If xl.Range(cell).Value = "" Then
rowtouse = i
i = 15999
End If
i = i + 1
Next

Set qry = db.QueryDefs("ChequestoPrint")
Set rs = qry.OpenRecordset(dbOpenDynaset, dbSeeChanges)
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst

For i2 = 1 To intRecCount

Dim supplier
Dim dateofcheque
Dim chequenumber
Dim details
Dim amount

supplier = Trim(rs("Name"))
dateofcheque = Replace(Trim(rs("dateprinted") & ""), "/", ".")
chequenumber = Trim(rs("chqno"))
details = Trim(rs("description"))
amount = Trim(rs("totalpayable"))

datemonth = Mid(dateofcheque, 8, 4)
dateday = Mid(dateofcheque, 2, 4)
dateyear = Mid(dateofcheque, 10, 4)

dateofcheque = dateday + "." + datemonth + "." + dateyear

xl.Range("A" + CStr(rowtouse)).Value = supplier
xl.Range("B" + CStr(rowtouse)).Value = dateofcheque
xl.Range("C" + CStr(rowtouse)).Value = chequenumber
xl.Range("D" + CStr(rowtouse)).Value = details
xl.Range("E" + CStr(rowtouse)).Value = amount

rowtouse = rowtouse + 1

rs.MoveNext
Next

xl.Visible = True

Set xl = Nothing

End Sub

 

[SkipVought]

Hi,

    Set xl = CreateObject("Excel.Application")
    'xl.Workbooks.Open (opendialog.File)
    xl.Workbooks.Open ("\\sv1\Finance\Management Accounts\Accounts 01.04.09-31.03.10.xls")
    xl.Sheets("Cheques Written").Select
    
    rowtouse = xl.cells(3,"C").end(xldown).row + 1
[s]
    For i = 3 To 16000
        cell = "C" + CStr(i)
        If xl.Range(cell).Value = "" Then
            rowtouse = i
            i = 15999
        End If
        i = i + 1
    Next
[/s]    
    Set qry = db.QueryDefs("ChequestoPrint")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}