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.
Step 1
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.
Video of the Day
Step 2
Click the "Tools" menu and click "References." Scroll down and check the box next to "Microsoft Excel
Step 3
Start by type the following VBA code to create a new sub procedure:
Private Sub createSpreadSheet()
Step 4
Continue by typing the following code to create three new variable objects that will be used to create the Excel spreadsheet:
Step 5
Add the following code to launch Excel and display it:
newExcelApp.Visible = True
Step 6
Type the following to add a new workbook and a worksheet:
Set newWkSheet = newWbk.Worksheets(1)
Step 7
Add data to your new spreadsheet by typing the following:
newWkSheet.Cells(1, 1).Value = "New worksheet..."
Step 8
Save the new workbook created by typing the following code:
Step 9
Edit the path on the previous step and type the path and file name of your choice.
Step 10
Type the following to end the sub procedure:
End Sub
Step 11
Press "F5" to run your procedure and create a new Excel spreadsheet.
Video of the Day