Excell Automation - releasing the application

[durbz]

Hi,

I have created a small program that produces graphs in Excell. It then saves the graphs in a file called "results.xls"
Everything works fine but I am unable to open the results.xls file it seems as though it is locked by the program.

I use

XL.Quit
Set oXL = Nothing
Set oChart = Nothing
Set oBook = Nothing
Set oSheet = Nothing

at the end of the processing hoping that this would free up the Excell application and file, but the file is still locked

can anyone tell me what I am missing?

Thanks

[Thoppae]

Hi,
Before Closing the application have you used

objExcelWorkbook.Close

Have you set the visible property of app true?. If true you can see the events thatz happening.
Regards,
Thoppae

[durbz]

I ssave and close the file with

oXL.ActiveWorkbook.Close SaveChanges:=True, fileName:=xlsfilename

I keep the application invisible because it speeds up processing.
When I make the application visible I am able to view the spreadsheet and graphs without any problem

[Thoppae]

Hi,
File is locked in the sense that you are not able to open the file right?. Check whether Excel.exe is still running in process of task manager. If it is running end the process and try to open the excel file again.
If the above doesn't helps, start debugging by simply opening the document and closing programatically and opening manually.
Regards,
Thoppae.

[durbz]

Yeah tried that, there is no instance of Excel in the task manager.

I havent tried opening and closing the files programaticaly and then viewing the results. Ill give that a bash!
Thanks

[durbz]

Right
I set the Visible property to true and this is what I have found

I can select a file and the select the menu option to create the graph - this works fine.

If I then try to select the menu option again to create the graph using the same file I get "Method of Object failed" error message. This is where the excel files are getting "locked" when the visible property was set to false

The weird thing is though now that the Visible property is set to true and I can see the excell spreadsheet during processing, if I stop and restart the program after the error is encountered, and leave the "hung" excel window alone, once I start the processing the error will never return the program will quite hapily process the files by opening up another instance of excel and working with that. As soon a s I clear the "hung" excel file, the error returns

This is weird, any help would be appreciated

[Thoppae]

once I start the processing the error will never return the program will quite hapily process the files by opening up another instance of excel and working with that. As soon a s I clear the "hung" excel file, the error returns

Can i consider the error what you said above as the error "Method of Object failed"?. In that case try to close the excel app whenever you encounter error.
Can you quote the few lines where exactly app throws this error.
Regards,
Thoppae.

[durbz]

Originally posted by Thoppae
Can i consider the error what you said above as the error "Method of Object failed"?. In that case try to close the excel app whenever you encounter error.

Yes error = "Method of Object failed"

The program saves the chart file as results.xls but when it hangs the excel spreadsheet is still called book1 because the program hasnt gotten to the part wher it closes and saves the file yet.

When I close the hung excel application the error persists
If I leave the hung excel application open and run the program again the error doesnt come back. The hung application is called book1 whilst all other automated excel spreadsheets are book2, maybe that has something to do with it?

Originally posted by Thoppae
Can you quote the few lines where exactly app throws this error.
Regards,
Thoppae.

Yes its here

'Figure out the maximum and minimum values for the chart
   extremes = "C:" & Chr(indiParams + 64)
  error is on this line ---->  maxVal = Application.WorksheetFunction.Max(Worksheets("Sheet1").Range(extremes))
   minVal = Application.WorksheetFunction.Min(Worksheets("Sheet1").Range(extremes))

I have two options
1. Create one chart with loads of line plots on it
2. Create loads of charts with 1 line plot on each

the error ("Method of Object failed") occurs at the same point in both the Chart_creation sub procedures.

If you like I will post all the code but be gentle :-)

[Thoppae]

Hi,
It seems to be clear that "Method of Object failed" is something like trying to copy without selecting anything. The statement where error occured may be invalid due to the situation similar to abovesaid.
Regards,
Thoppae.

[durbz]

I think you are correct.
Further investigation shows that after I have plotted my first graph, all other graphs are plotted using the data that was stored in the first workbook even though the graphs are actualy created in seperate workbooks. Therfore if I leave the first workbook open, the application will happily proccess away. But if I then close that first workbook, the program cant find the data to plot the graph and gives the error.

I need to find wher the first workbook is being refrenced and dlete it at the end of processing.
I am using global variables so I think this could be my problem.

Maybe I should try to loosly couple my procedures more than Im doing

[durbz]

Right Im still stuck on this but have more info.

Excell closes down correctly after the program runs

oXL.Quit
Set oXL = Nothing
Set oChart = Nothing
Set oBook = Nothing
Set oSheet = Nothing

When I look in windows task manager there is nothing running under the applications tab but in the processes tab there is an instance of Excel.exe even though Excel has closed down????

When I end this Excel.exe process manualy the program runs fine, when I dont it hangs (as described above)

Now if someone knows how to remove this Excel.exe process programaticaly I would be forever greatfull and my work will be done here :-)

Just for the record this is how I start Excel

Set oXL = CreateObject("Excel.application")
' create a new workbook
Set oBook = oXL.Workbooks.Add
' Create a worksheet
Set oSheet = oBook.Worksheets.Item(1)

Thanks for any replies

[durbz]

For those that were following this or for those that come across this problem in the future this might help

http://support.microsoft.com/default...;EN-US;Q178510

I was using automation to create and save the Chart spreadsheet, unfortunately the application is not released from memory after the spreadsheet is automatically saved.
This is not a bug but a feature :-) (read above link)

I altered my code to allow the user to save the spreadsheet manualy instead of the program doing it automaticaly. The users would then close down Excel application when they have finished. This clears the entry of excel in memory

Cheers

Thanks Thoppae for helping