Creating an Excel spreadsheet in Access can save you time when you need to export data to an Excel spreadsheet. Visual Basic for Applications (VBA) can be a powerful tool to automate processes in Microsoft Office applications. With VBA you can use the Microsoft Excel Application object to create an Excel spreadsheet from Access. The Excel application object is the entry point to the rest of the object model. Export data faster to Excel by saving data directly to an Excel spreadsheet.
Launch Microsoft Office Access, click "Blank Database" and click the "Create" button to create a blank database. Click the "Database Tools" tab and click "Visual Basic" to open the Microsoft Visual Basic Window. Click the "Insert" menu and click "Module" to insert a new code module.
Click the "Tools" menu and click "References." Scroll down and check the box next to "Microsoft Excel
Start by type the following VBA code to create a new sub procedure:
Private Sub createSpreadSheet()
Continue by typing the following code to create three new variable objects that will be used to create the Excel spreadsheet:
Dim newExcelApp As Excel.Application Dim newWbk As Excel.Workbook Dim newWkSheet As Excel.Worksheet
Add the following code to launch Excel and display it:
Set newExcelApp = Excel.Application
newExcelApp.Visible = True
Type the following to add a new workbook and a worksheet:
Set newWbk = newExcelApp.Workbooks.Add
Set newWkSheet = newWbk.Worksheets(1)
Add data to your new spreadsheet by typing the following:
newWkSheet.Cells(1, 1).Value = "New worksheet..."
Save the new workbook created by typing the following code:
Edit the path on the previous step and type the path and file name of your choice.
Type the following to end the sub procedure:
Press "F5" to run your procedure and create a new Excel spreadsheet.