DISCOVER
×

How to Update a Spreadsheet From Access

Updated July 20, 2017

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:

Exit_updateSpreadSheet:

Err_updateSpreadSheet:

End Sub

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

bibliography-icon icon for annotation tool Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.