We are Access Experts. Call Us Today! 773-809-5456

Any Access version → Why Excel has a problem when using code in access

When you use code to refer to Excel (and other programs as well) you need to be careful not to use any code that does not refer back to the instantiated application object. If you do so then you will find that Access instantiates another Excel (or other program) object which is not visible to you, nor is it able to be referenced in any code. So, it will remain open until you close Access, kill the instance from the Task Manager, or use code to kill all instances of a program. Killing all objects of a certain type can be bad because your users may have Excel (or the other program) open and using it for other things so the code to kill all instances will shut those down as well.

So, what do we mean by not using code that does not refer to an instantiated object? Here is an example.
Suppose you have an Excel file at C:\Temp\MyExcelFile.xlsx that you are wanting to open and then manipulate via code from Access. You would do something like this:

Opened with Early Binding (setting a reference to Excel in Tools >References)
Dim objXL As Excel.Application
Dim xlWB As Excel.Workbook

Set objXL = New Excel.Application
Set xlWB = objXL.Workbooks.Open("C:\Temp\MyExcelFile.xlsx")

You could also do it with late binding like:
Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("C:\Temp\MyExcelFile.xlsx")

So now you want to refer to the active sheet on the open workbook. How do you do it?

If you do it like:
ActiveSheet.Range("B1").Value = "Something"
Then you just have let Access instantiate that hidden instance of Excel because you have not tied the ActiveSheet to the specific application object you have purposely instantiated. The code will run okay the first time but then it will cause a problem if you try to run the code again before closing Access or before killing the hidden instance.

The correct method would be to use something like:
objXL.ActiveSheet.Range("B1").Value = "Something"
or
xlWB.ActiveSheet.Range("B1").Value = "Something"

Either way should work but since each object is tied directly to the purposefully instantiated object, then there is no problem when you finally quit that object and the code can run again without problem because there is no hidden instantiated Excel Application object.

So I hope that helps you understand why you must always tie the other code into one of the purposefully instantiated objects.