How to Access VBA to Create an Excel Spreadsheet

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
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.

Advertisement

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.

Advertisement

Video of the Day

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:

Advertisement

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:

Advertisement

newExcelApp.Visible = True

Step 6

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

Set newWkSheet = newWbk.Worksheets(1)

Advertisement

Step 7

Add data to your new spreadsheet by typing the following:

Advertisement

newWkSheet.Cells(1, 1).Value = "New worksheet..."

Step 8

Save the new workbook created by typing the following code:

Advertisement

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

Advertisement

Advertisement

references