Microsoft Access is a database application created by Microsoft. Access provides a very easy user interface to work with databases. It also allows the user to create macros to automate tasks. In this tutorial we will learn to integrate Microsoft Excel and Access and see how to open an excel file from access.
Step # 1 – Macro builder
In Microsoft Excel and Access, macros can either be created as standalone, or can be triggered by an event. We will be creating a standalone macro in this tutorial. To start, we will navigate to the “create” tab in the ribbon and select the macro option.
After that, Macro builder window will open up. In this window, we have to list actions that the macro must perform. In this tutorial our intent is opening an Excel file. In the drop down menu, we will select the Runcode option.
Step # 2 – Create module
Navigate to the “database tools” tab in the ribbon and click the visual basic option.
A new window opens that allows us to create and edit visual basic code. Here we will create a new module by clicking the Insert Module button on the toolbar. The button is the second one from the left.
Once the module is created, a new window appears in the Visual Basic editor. There we will create a function called openexcelfromaccess(). The function will declare a myXL object, setting it to create an object of excel application type, and then open the excel workbook by the workbooks.open() function. The workbooks.open() function takes in the path of the excel file that we need to open as an argument.
Step # 3 – Run Code
Once the function is complete we will assign it to the Runcode operation back in the macro builder and save it.
And this is how we can open an excel file from access via macro.
Home