How to access excel files from MS Access

Below is a piece of code which shows how you can access Excel files from an Access database.  To make things generic, I have used a late binding approach rather than early binding, this will ensure that this example works on all of your machines, provided you have some version of Microsoft Excel on your machines. 

I will try to discuss in another blog what the differences are in using these two approaches.

Before you begin, you will need to create a form with a button, in this button you will need to add the code below.


Download the code from HERE

There are a few assumptions before you run this code.

1.  You have created a blank form with a button.  As you see from the code my button has a name of command15_click()

2.  You have saved an Excel file named "template.xls" in the "c:\learn\" directory, please refer to row #6.

3.  The file template.xls has a sheet named "Main", please refer to row #7.

Now to give a brieft description of what the code does.

Rows 1-3, these are merely variable declarations.  You will notice that the types are Object as I am using the early bind method.

Row 4, I have created an Excel Object, when the compiler runs it checks what available Excel version has been installed and creates an object.

Row 5, This makes the Excel object visible.  You can opt to make this false if you fancy.

Row 6, I have opened up the template.xls and assigned this to object ObjWb.

Row 7, I have set object objWs to sheet "main"

Row 8, I have assigned the value "hello world" to range "A1"

Row 9, Saves the file as "hello.xls"

Row 10-15, These serve as clean-up lines.  Whenever you are using the "set" command ensure that you release these from memory by setting them back to nothing.