Automation Error

[Creigbm]

I keep on receiving an 'Automation Error' at random times which doesnt allow me to modify my macros. Any ideas what causes this error?

 

[CajunCenturion]

Generally that error occurs when you are trying to instantiate an instance of another application, and for some reason, an object cannot be created.

There are many possible causes.
- Registry error
- Missing files
- Not enough memory
- Invalid intialization parameter
- Invalid pathnames,
and on and on.

To solve the problem, I would suggest isolating the problem to a specific line of code and investigating from there. Without knowing more about your situation, it's hard to be any more specific than that.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

Thanks Cajun. Its odd because this is the order of things I have done to get this error.

Run Macro
Edit Macro (just to make a change to the userform visually)
Run Macro - Automation Error.

I have tried to restart my comp numerous times and it seems like the error pops up at random. For instance, I received the error about 20 min ago. I just restarted my comp and the macro worked great...no error. I just ran it, didnt change a thing. Is vba that unstable that restarting your comp will fix most problems? I know I will get the same error in about 20 more min since I was running into it all day yesterday. In any case, thanks for your input.

 

[CajunCenturion]

Can't be sure, but I don't think it is a VBA error, nor do I believe that it indicates that VBA is unstable. VBA, like most apps, is dependant on a stable operating environment. Rebooting the machine does nothing to VBA, but it does reset the operating environment.

You may have a system configuration problem.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

It seems like this is the section of code it is getting hung up of.

Public Function ShowOpen(rCol_FilePatterns As Collection) As String

   Dim lTyp_OpenFileName         As OPENFILENAME
   Dim lTyp_SaveFileName         As OPENFILENAME
   Dim lStr_FileSel              As String
   Dim lStr_FilePattern          As String
   Dim lInt_Idx                  As Integer
   Dim lStr_FileSet()            As String
   
   lStr_FilePattern = vbNullString
   For lInt_Idx = 1 To rCol_FilePatterns.Count
      lStr_FileSet = Split(rCol_FilePatterns.Item(lInt_Idx), "::")
      lStr_FilePattern = lStr_FilePattern & lStr_FileSet(0) & Chr(0) & lStr_FileSet(1) & Chr(0)
   Next lInt_Idx
   
   With lTyp_OpenFileName
      .tLng_StructSize = Len(lTyp_SaveFileName)
      .tLng_hWndOwner = 0
      .tLng_hInstance = 0
      .tStr_Filter = lStr_FilePattern
      .tStr_File = Space(254)
      .tLng_MaxFile = 255
      .tStr_FileTitle = Space(254)
      .tLng_MaxFileTitle = 255
      .tStr_InitialDir = "C:\"
      .tStr_Title = "Select Spread Sheet to Import"
      .tLng_flags = 0
   End With

   If (GetOpenFileName(lTyp_OpenFileName)) Then
      lStr_FileSel = Trim(lTyp_OpenFileName.tStr_File)
   Else
      lStr_FileSel = vbNullString
   End If

   ShowOpen = lStr_FileSel

End Function

I basically have the macro initiating excel and saving the data from a flexgrid into the spreadsheet. This approach works fine (and still does) in other macros, just not this one. Is there a set of references that must be loaded for this to work?

 

[CajunCenturion]

The code shown does not use any automation, but rather, makes a call to a system API (GetOpenFileName), which presumably is declared elsewhere.

Can you narrow down your search to a specific line of code? Of particular interest may be the application code that follows this routine; that is, the code that tries to process the spreadsheet selected in the above routine.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

I think it lies in this line here:

Set ExcelSheet = GetObject(txtImportFile)
Set xlsh = ExcelSheet.ActiveSheet

If I get rid of this code it works... The thing is I have used this exact same code before with no problems.

 

[CajunCenturion]

What is the value of txtImportFile when it fails? when it succeeds?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

The txtImportFile variable is simply the file path name that comes from the open dialog box. If it fails (i.e. txtImportFile = "") then nothing happens.

 

[CajunCenturion]

What would you expect the GetObject function to return if txtImportFile does not contain a valid pathname?

Are you checking to ensure that txtImportFile is a valid pathname, i.e. that a file was actually selected from the open dialog box, before trying to automate the file?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

Good point. I am not checking to ensure the pathname is valid. I tried entering a nonvalid pathname when running another macro and I get a runtime error. I can simply add an error checker to resolve that but I am not getting the same automation error. The other macro will not even run to allow me to get to the GetObject part. Thats the kicker. If I remove that bit of code (even though I am not running it) then the macro works. If I leave it in, the macro will not even run (the line of code in question is not accessed). Hope that makes sense.

 

[CajunCenturion]

On exactly which line of code does the error occur?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

I have been trying to narrow it down and all it says it there is an error in "sub main()" Here is what I did today to reproduce the error:

1) Ran Macro - works
2) Edit Macro - works
3) Edit Macro - works
4) Edit Macro - does not work

The only thing I editted was the number of decimal places I was rounding a number to. I cant seem to free up the mememory after running a macro (i.e. I am not ending the program propely). Do you have a recommendation as to a good practice to clear the memory after exiting a program? That is the only thing I can think of at this point seeing that I can do the same thing twice and get two different results.

Thanks for you help in getting this resolved

 

[CajunCenturion]

Are you terminating and releasing all of the objects that you are creating. For example:
Here is an instantiation of an Excel object

Set cExl_Appl = CreateObject("Excel.Application")

Later in the code, when I'm done with it, I close and release it

cExl_Appl.Workbooks.Close
cExl_Appl.Quit
Set cExl_Appl = Nothing

If you are creating objects and not releasing them, then you could be eating up your memory.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

I do not believe I am releasing all of the objects. If I am not using a MS based program (excel, word, etc.), would I simply need to do the .quit and set the object to nothing? I am currently setting all the variables to nothing but not the objects.

 

[CajunCenturion]

Whether or not you need to 'quit' and object depends on the object. Some objects, like Excel, do require a 'quit' before destroying the object. Other applications may not.

You need to check in the documentation for the specific application that you are using to know how best to terminate it.

You really do not need to set all of your variables to nothing, as the system should take care of them when they go out of scope. But you should set all of the objects to nothing to release that memory.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Creigbm]

Here's an update on this error. If I have the VBA editor opne, then the error does not occur. As soon as I close it and try to run the macro, then I get the error and nee to restart my comp to get it running again. Like I said before, the automation I am using is excel, very simple. It does not even have a change to run the code, it simply gives me the automation error and doesnt let me run or modify the code. Its odd because I have other macros with the EXACT same code and it works file.