Copying data from a MS Access form into Excel

I have code that takes fields from a MS Access form and copies the data into a saved Excel file. The first record in Access in imported to Excel with a range of A2:I2. The second record in Access is imported to Excel with a range of A3:I3, and so on.... What currently happens now is if I close my form in Access and open it back up, and say I already had two records imported into this same Excel file, and now I want to add a third record, it will start over at the first row (A2:I2) and write over what is already there. My question is how can I, if I close and open Access keep it from starting over on (A2:I2), and instead start at the next available row, which to follow the example given would be (A4:I4)? This is the code I have

Private Sub Command73_Click()
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("Y:\123files\Edmond\Hotel Reservation Daily.xls")
objXLApp.Application.Visible = True

With objXLBook.ActiveSheet

Set r = .usedRange
i = r.Rows.Count + 1

.Cells(i + 1, 1).Value = Me.GuestFirstName & " " & GuestLastName
.Cells(i + 1, 2).Value = Me.PhoneNumber
.Cells(i + 1, 3).Value = Me.cboCheckInDate
.Cells(i + 1, 4).Value = Me.cboCheckOutDate
.Cells(i + 1, 5).Value = Me.GuestNo
.Cells(i + 1, 6).Value = Me.RoomType
.Cells(i + 1, 7).Value = Me.RoomNumber
.Cells(i + 1, 8).Value = Date
.Cells(i + 1, 9).Value = Me.Employee
End With

Set r = Nothing
Set objXLBook = Nothing
Set objXLApp开发者_StackOverflow中文版 = Nothing

End Sub

---

You can get the last used row:

Set r = objXLBook.ActiveSheet.UsedRange
i = r.Rows.Count + 1

Some notes.

Private Sub Command73_Click()
''It is always a good idea to put sensible names on command buttons.
''It may not seem like much of a problem today, but it will get there
Dim objXLApp  As Object
Dim objXLBook  As Object
Dim r As Object
Dim i As Integer

''It is nearly always best to check whether Excel is open before 
''opening another copy.
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open( _
      "Y:\123files\Edmond\Hotel Reservation Daily.xls")
objXLApp.Application.Visible = True

''It is generally best to specify the sheet
''With objXLBook.ActiveSheet

With objXLBook.Sheets("Room Reservation")

    ''If the used range includes empty rows 
    ''it may not suit
    ''Set r = .UsedRange
    ''i = r.Rows.Count + 1

     ''From comments, it appears that the data is dense
     ''but with a number of empty rows at the end of the sheet

     i = .Range("A1").End(xlDown).Row + 1

    .Cells(i, 1).Value = Me.GuestFirstName & " " & GuestLastName
    .Cells(i, 2).Value = Me.PhoneNumber
    .Cells(i, 3).Value = Me.cboCheckInDate
    .Cells(i, 4).Value = Me.cboCheckOutDate
    .Cells(i, 5).Value = Me.GuestNo
    .Cells(i, 6).Value = Me.RoomType
    .Cells(i, 7).Value = Me.RoomNumber
    .Cells(i, 8).Value = Date
    .Cells(i, 9).Value = Me.Employee

End With

''Tidy up
Set objXLBook  = Nothing
Set objXLApp = Nothing

End Sub

You might also like to look at TransferSpreadsheet.

Another possibility is to use the RecordsetClone, for data from a form, or any recordset, for that matter. It does not give quite the same control, but it is very fast:

Dim objXLApp As Object
Dim objXLBook As Object
Dim r As Object
Dim i As Integer
Dim rs As DAO.Recordset

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
Set objXLBook = objXLApp.Workbooks.Open( _
      "Y:\123files\Edmond\Hotel Reservation Daily.xls")

Set rs = Me.RecordsetClone
With objXLBook.Sheets("Sheet1")

    Set r = .UsedRange
    i = r.Rows.Count + 1

    .Cells(i, 1).CopyFromRecordset rs

End With