Excel behaving badly. (1 Viewer)

[Fuga]

Hi,

I´m having trouble with an automated process.

First, access exports a table to a text file.
Second, excel opens the file and runs a macro on a workbook that contains linked data to the file.

The workbook is linked back in access.

A few days ago I started having problems. Sometimes the file can´t be exported because of authority problems (this is because the excel object doesn´t quit after having done the operations but locks the file so that the next time the process runs, it halts)

Question: Is there a command in VBA that absolutely certain kills the instance of excel? (in worst case I´m ok with a command that kills all instances of excel)

I also have problems within the macro itself. Excel complains about the "range method of global failed" or something like that.

The thing is, it works when I run just this part of the process, but not combined with a few others.

It worked fine until a few days ago, and the only thing I´ve done is that I have added a few graphs on the main form.

Has anyone had similar experiences? Might there be some sort of memory insufficiency?

Fuga.

 

[FoFa]

Destroying an Instance of Microsoft Excel
When manipulating Automation servers, it is very important to destroy any object variables associated with the instances, as well as to close the instance if your code created it. If not, your code could be opening instances of the server without closing them, and if run repeatedly, could eventually consume too many resources (such as memory) on the user's machine, resulting in performance degradation. This topic demonstrates how to correctly close an instance of Microsoft Excel and destroy any object variables associated with it. For more information about destroying object variables, see Destroying Automation Objects.

When Does an Instance of Microsoft Excel Close Automatically During Automation?
An instance of Microsoft Excel closes automatically when its object variable loses scope or is set to the keyword Nothing if there are no workbooks open, and the Application object's UserControl property is set to False.

How Do I Close an Instance of Microsoft Excel?
There are two different methods for closing the instance of Microsoft Excel that your code is automating. Regardless of which method you choose, your code should be responsible for destroying any object variables by setting them to the keyword Nothing. For information about whether your code should Close the Instance of Microsoft Excel, see Determining If You Should Close the Instance of Microsoft Excel.

Method 1: Use the Application Object's Quit Method
Option Explicit
Dim xlApp As Excel.Application

Sub CloseExcel()

Set xlApp = CreateObject("Excel.Application")
'Other Automation code here

xlApp.Quit
Set xlApp = Nothing
End Sub

Method 2: Closing All WorkBooks and Setting the UserControl Property to False
Option Explicit
Dim xlApp As Excel.Application

Sub CloseExcel()

Set xlApp = CreateObject("Excel.Application")

'Other Automation code here

xlApp.WorkBooks.Close 'Close all open workbooks
xlApp.UserControl = False
Set xlApp = Nothing

End Sub

 

[boblarson]

Also, just to note something I just learned last week while troubleshooting a similar issue with someone on the forum, is that if you use a reference in code like: Activesheet.Range("A1").Select, then Access will hold open the Excel object, even if you try to use Quit and set it to nothing. This will stay open until Access closes.

To get around that, always use the qualified object:

so if I have set xlWB to my workbook object, then referring to the code above to refer to the activesheet on it would be xlWB.Activesheet.Range("A1").Select. Then, if you close the xlWB object and set it to nothing and Quit and then set the Excel application to nothing, it releases properly.

 

[Fuga]

Thank you both very much.

I think I solved with a workaround. Excel really wasn´t quitting, so I had to set it to nothing in the middle of the process, and then creating it again.

What puzzles me is that I´ve never had this problem before...

Fuga.

 

[Fuga]

well, the problem came back.

It´s all very strange. When I open the module and hit f5, the process works. But when I call it from another module, it doesn´t.

Very annoying. The whole db is quite useless without it. Could it be that the computer can´t handle it?

Fuga.

 

[RoyVidar]

In some very, very rare circumstances, there is some corruption of the Excel files/DDLs or whatever they're called, but usually, there's som little part of the code where you've forgotten to "anchor" methods, properties or objects of Excel to their parent object, as stated by boblarson.

This is very hard to spot without seing the code.