0

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 = Nothing

End Sub
Community
  • 1
  • 1
Edmond
  • 149
  • 2
  • 7
  • 20

1 Answers1

0

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • @Remou. do i need to declare r as an object? – Edmond Feb 18 '11 at 16:04
  • @Edmond r is either a Range or an Object. You appear to be using late binding, so Object would be good. – Fionnuala Feb 18 '11 at 16:16
  • @Remou when i declare r as an object it doesnt do anything but open my excel file. Does it matter where i put your add in? – Edmond Feb 18 '11 at 16:24
  • @Edmond Just before `i` would be good. It is intended as a way to get a count of rows so you can set `i` to the proper number, rather than `i = i + 1`, which could lead anywhere if 'i' is not declared as a numeric type. – Fionnuala Feb 18 '11 at 16:29
  • @Remou. So i have Dim r as object in the declarations.... and your code just above i=i+1. When I run it, it doesnt paste anything to my excel file, it just opens it. – Edmond Feb 18 '11 at 16:34
  • @Remou. I adjusted the code, but it still only opens my Excel file. And with the transferspreadsheet, wouldnt that only import Excel into Access? – Edmond Feb 18 '11 at 17:16
  • TransferSpreadsheet works both ways, export and import. I tested before I posted, and it worked for me. What happens when you comment out the line I suggested, does it go back to working the way you expected? – Fionnuala Feb 18 '11 at 17:25
  • @Remou. Really?? I have not tried the TransferSpreadsheet. But with both of the suggested code addons it just opens my file. If i comment out the line you suggested it goes back to the orignal setup. – Edmond Feb 18 '11 at 17:35
  • So it just opens your file and does nothing else? Please edit you post to show the code you are using. – Fionnuala Feb 18 '11 at 19:11
  • @Remou. Not sure where the Set r =Nothing goes. – Edmond Feb 21 '11 at 20:34
  • It goes at the very end and is not essential, but you should `Set objXLBook = Nothing` and `Set objXLApp = Nothing` – Fionnuala Feb 21 '11 at 20:39
  • @Remou. Ok i found out it does work but it starts importing down on row 999 – Edmond Feb 21 '11 at 20:46
  • In that case you have a number of empty rows in your used range. You can see this by choosing ctrl+end. You can either clean up your file or use another way of getting the last cell in the range, such as xlUp from the end. – Fionnuala Feb 21 '11 at 20:55
  • @Remou. I think I know why this is...I have another tab that takes the imported information and highlights the check in and check out dates next to the said Room Number. This Chart reads everything from row 2 down to row 997. Is there a way to have it import it from the first row? – Edmond Feb 21 '11 at 20:56
  • I did not quite catch that, what do you mean by "first row"? Have a look at http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro/71310#71310 – Fionnuala Feb 21 '11 at 21:02
  • @Remou. Ive looked at the question you've referred me too. What I have works, I just need it to start importing at cell A2, not cell A998. To answer your question, can I send you my Excel file I'm using to hold the reservations? – Edmond Feb 22 '11 at 16:05
  • The question I referred you to has a method of getting the last row. Why A2? Originally, you wished to continue on from the last entered line. You seem to have changed the requirements? There is no point in sending me a file when I do not know what should be happening. – Fionnuala Feb 22 '11 at 16:10
  • I would have provided a description to go along with the visual. But its reading my last cell used as A998 bc the worksheet is a chart that goes down to cell A997. So im saying A2 bc when I actually implement the database I would like for it to start with the first row. – Edmond Feb 22 '11 at 16:32
  • Okay, go ahead, you have my address. – Fionnuala Feb 22 '11 at 16:38
  • @Remou When I run the code that you provided me, I get a Compile Error: Variable not defined with the (xldown) highlighted? – Edmond Feb 23 '11 at 22:43
  • `xlDown` is a built-in constant for excel, it is equal to `-4121`, so you can just substitute, or add a line `xldown=-4121`. – Fionnuala Feb 23 '11 at 22:47
  • Did you close everything before you tried again, including checking that you do not have hidden instances in taskmanager? – Fionnuala Feb 23 '11 at 23:12