Moving to the next line to populate an excel file from VBA

Question

I have the below code that takes certain fields from my MS Access (A small Hotel Reservation Database)form and populates defined cells in the said Excel file.

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("Y:\123files\File\Hotel Reservation.xls")
objXLApp.Application.Visible = True

objXLBook.ActiveSheet.Range("B2") = Me.GuestFirstName & " " & GuestLastName
objXLBook.ActiveSheet.Range("C2") = Me.PhoneNumber
objXLBook.ActiveSheet.Range("E2") = Me.cboCheckInDate
objXLBook.ActiveSheet.Range("F2") = Me.cboCheckOutDate
objXLBook.ActiveSheet.Range("H2") = Me.RoomType
objXLBook.ActiveSheet.Range("I2") = Me.RoomNumber
End Sub

How can I keep populating a new Guest to the same Excel file just on the next row?

Answer 1

Or to just provide a simple answer for the exact question here, if you (perhaps for other reasons) really want to do this via VBA. (And to be honest, my recommendation would be create a report in access and dump the excel file altogether), the following very simple VBA code will do what you want...

Dim i as Integer 
i = ObjXLBook.ActiveSheet.UsedRange.Rows.Count 
ObjXLBook.ActiveSheet.Cells(i + 1, 2).Value = Me.GuestFirstName & " " & GuestLastName 
ObjXLBook.ActiveSheet.Cells(i + 1, 3).Value = Me.PhoneNumber 

Answer 2

You should read http://office.microsoft.com/en-ca/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx, it'll help your link your MS Access data with a MS Excel spreedsheet, no need of VBA in this simple case.

excerpt:

Getting started with the export operation

ShowStep 1: Identify the data that you want to export

Start by locating the database and the object in the database that has the data you want to export. You can export a table, query, form, or a report. For example, you can export the customer data stored in the Customers table, or the entire Products catalog report.

Note You cannot export data access pages (data access page: A Web page designed for viewing and working with data from the Internet or an intranet. Its data is typically stored in an Access database.), macros, and modules.

ShowStep 2: Decide where to start the export operation

....

You can export an object from the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), or when it is open in a view. The following table describes how the view impacts what is exported.

Note You cannot export data from Design view or SQL view. Object View/Window What gets exported Table, Query, Form Database window All fields and records Table, Query, Form PivotTable and PivotChart views All fields and records in the underlying record source, irrespective of whether the fields are actually included in the view. Table, Query, Form Datasheet view If you want to export only some of the data, you can select it, and then choose to export only the selected data. You also have the option of exporting the entire datasheet. Form Form view All fields and records in the underlying record source, irrespective of whether the fields are actually included in the view. Report Database window, Print Preview, and Layout Preview All the data contained in the text boxes in the Group Header and Detail sections, and any text box in a group footer containing an expression with the Sum function. Access uses the outline feature of Excel to format the report in Excel. For more information about how a report is exported to Excel, see How reports are output to Microsoft Excel. For more information about how to work with the report in Excel, see Topics on working with outlines in Excel Help.

ShowStep 3: Identify the destination file for the export operation

During the export operation, you will be prompted to specify the name of the destination file. If a file with the name you specify doesn't exist, a new file will be created. If the file exists, one of the following things will happen:

* If you are exporting a table or query and you don't select the Save Formatted check box during the export operation, the file will not be overwritten. A new worksheet will be added to the file with the same name as the object that is being exported. If a worksheet already exists with that name, Access will prompt you to either replace the contents of the corresponding worksheet, or specify a different name for the new sheet.

Selecting the Save Formatted check box makes the worksheet inherit the format settings similar to the datasheet, but overwrites the existing contents of the worksheet.

* If you are exporting a form or a report, the file will always be overwritten. All of its existing worksheets will be removed, and a new worksheet with the same name as the exported object will be created.

ShowStep 4: Things you should know before exporting certain data types and controls

* Pictures and objects    All graphical elements, such as a logo, background picture, and contents of OLE object fields that are part of the exported data will not be exported.
* Graph    When you export a form or a report that contains a Microsoft Graph object, the graph object does not get exported. To resolve this situation, see How to export a graph from Access to Excel.
* Null values    Sometimes, Null values get replaced with the data that should be in the adjacent column in the resulting worksheet. For more information on when and why this problem occurs, and how to resolve it, see Nulls replaced with next field's data when you export to Excel.
* Calculated values    The expression that is used to calculate the values is not exported to Excel. Only the results of the expressions get exported.
* Date values    Date values earlier than Jan 1, 1900 do not get exported — the corresponding cell in the worksheet will contain a Null value, instead. For more information about how to resolve this, see Exporting dates may result in nulls or cause "Numeric Field Overflow" errorExporting dates may result in nulls or cause "Numeric Field Overflow" error.
* Check boxes    If you start the export operation from the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) or in Form view, check boxes on forms will not get exported. The corresponding column in the worksheet will display "#". To solve this situation, open the form in Datasheet view before exporting it. The corresponding column in the worksheet will contain TRUE or FALSE, depending on the selected status of the check box in the form.
* Subreports and subforms    Subreports are exported, but subforms will be ignored.

ShowStep 5: Start the export operation

1. If the object you want to export is not open, in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click the name of the object. To save only a part of a datasheet, open the datasheet and select that portion of the datasheet before continuing.
2. On the File menu, click Export.

3. In the Save as type box, click Microsoft Excel 5-7 or Microsoft Excel 97-2003.

   Note If you do not see Microsoft Excel in the Files of type box, it is because the path to the required driver in the registry is invalid. For more information about how to correct this problem, see Could not find installable ISAM error message.

4. Click the arrow to the right of the Save in box, and select the drive or folder to save to.

5. In the File name box, enter a name for the file (or use the suggested name).
6. Select the Save formatted check box.
7. Do one of the following: * If you are saving a datasheet, click Export All to save the entire datasheet or Save Selection if you selected a portion of the datasheet in step 1. * For all other database objects, click Export.

ShowStep 6: Review the Excel worksheet

Open the worksheet and make sure the data got exported completely. Look for error indicators on cells (green triangles) or error values (strings beginning with "#", instead of the data). For more information on troubleshooting error indicators and error values, see Excel Help.

While reviewing the worksheet for errors, also look for blank or missing columns, and empty cells. If you find major problems, correct them in the source database, and repeat the export operation.

For detailed troubleshooting information, see Troubleshoot exporting in Access. Other ways to bring Access data into Excel

In addition to exporting, you can use the following techniques to bring data into Excel from Access.

* Cut or copy data from Access and paste it into an Excel worksheet. For more information about how to do this, see the section "Copy or move records or data from multiple fields in Microsoft Access to another application" in the topic Copy or move data.
* Export data by using code. You can write a macro or a Visual Basic for Applications (VBA) procedure to export data programmatically. For more information about how to do this, see Export data programmatically.
* Load Access data in an instance of Excel.

ShowHow?

1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click the name of the datasheet, form, or report you want to save and load into Excel. To load only a part of a datasheet, open the datasheet, and then select that portion of the datasheet before continuing.

2. On the Tools menu, point to Office Links, and then click Analyze It with Microsoft Excel.

   • Export Microsoft Access data to an Extensible Markup Language (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) (XML) file, which can then be imported into Excel. For more information about how to export Access data as XML, see Export Access data as XML.

....