How to Access VBA to Create an Excel Spreadsheet

Save data to an Excel spreadsheet using VBA.

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.

Step 2

Click the "Tools" menu and click "References." Scroll down and check the box next to "Microsoft Excel Object Library." Click "OK."

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:

Dim newExcelApp As Excel.Application
Dim newWbk As Excel.Workbook
Dim newWkSheet As Excel.Worksheet

Step 5

Add the following code to launch Excel and display it:

Set newExcelApp = Excel.Application

newExcelApp.Visible = True

Step 6

Type the following to add a new workbook and a worksheet:

Set newWbk = newExcelApp.Workbooks.Add

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:

newWkSheet.SaveAs ("C:\myworksheet.xlsx")

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.

references