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.
- Skill level:
Other People Are Reading
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 <version number> 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:
Dim wkBkObj As Excel.Workbook Dim XLSheet As Excel.Worksheet
Open an existing Excel workbook and use Sheet1 as the sheet to update by adding the following code:
Set wkBkObj = Workbooks.Open("C:\mySpreadsheet.xlsx") Set XLSheet = wkBkObj.Worksheets("Sheet1") wkBkObj.Windows(1).Visible = True
Add the following VBA code to update A1 of the spreadsheet:
With XLSheet .Range("A1").Select .Range("A1").Value = "updated value from Access" End With
Save, close the Excel workbook, and end the procedure by adding the following code:
MsgBox Err.Description Resume Exit_updateSpreadSheet:
Press "F5" to run the procedure and update your spreadsheet.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for