How to Update a Spreadsheet From Access

Written by jaime avelar | 13/05/2017
How to Update a Spreadsheet From Access
Export data to Excel from Access. (Ryan McVay/Photodisc/Getty Images)

Microsoft Access and Excel almost go hand in hand when developing database applications in Access. Users love to analyse their data using Excel, and you can give them that functionality by being able to update spreadsheets from Access. In Access you can use Visual Basic for Applications to open an existing Excel spreadsheet and update it with data dynamically. In VBA you can use the Microsoft Excel Object Library to manipulate Excel objects.

Click the "Database Tools" tab, click "Visual Basic," and click the "Insert" menu. Click "Module" to insert a new VBA code module.

Click the "Tools" menu and click "References." Check the box next to "Microsoft Excel Object Library" and click "OK" to add the reference.

Start by typing the following code to create a new sub procedure:

Private Sub updateSpreadSheet()

On Error GoTo Err_updateSpreadSheet:

Type the following code to create the workbook and worksheet objects:

Open an existing Excel workbook and use Sheet1 as the sheet to update by adding the following code:

Add the following VBA code to update A1 of the spreadsheet:

Save, close the Excel workbook, and end the procedure by adding the following code:



End Sub

Press "F5" to run the procedure and update your spreadsheet.

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.