cdo attach fails - file in use

[bbxrider]

my program creates an .xls and after saving it, i thought i closed down everything to do with the file,
ExcelWorkbook.SaveAs workDir & Format(Now, "yyyyMMdd") & "topSites.xls"
ExcelWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing
Set ExcelWorkbook = Nothing
Set ExcelSheet = Nothing

at a later point trying to attach the file to an email with cdo
objMessage.AddAttachment newTopSiteFile

and i get -2147024864 80070020 process cannot access the file because it is being used by another process.
there is nothing else running on the machine that could possibly be using the file, somehow the program still has it tied up?
even the 'unlocker' utility finds no locking handle?
after the program ends if i restart it, and go directly to the attach, it works then
something else with excel needs to be released?

[westconn1]

maybe you are not giving enough time for the excel file to be released, put a sleep in there, or loop on error till it succeeds

[si_the_geek]

The order you release the objects is not ideal, you should Set things to nothing after the last usage (and before closing the parent object), eg:

ExcelWorkbook.SaveAs workDir & Format(Now, "yyyyMMdd") & "topSites.xls"
Set ExcelSheet = Nothing
ExcelWorkbook.Close
Set ExcelWorkbook = Nothing
ExcelApp.Quit
Set ExcelApp = Nothing

There is a good chance that the problem(s) are cause by something else in your code... to find out, run that part of the code, and while your program is still open go to Task Manager and check if Excel is still there. If it is, you have issues that need to be fixed.

Another way to check is to run that part of your code twice (without stopping your program in between) and see if you get any 'strange' errors.

[bbxrider]

thanks, i guess that was it, i wish i knew why the ordering is important. there must be something basic about those objects i don't know about. on the surface it seems like were closing 3 doors to the house, front back and side, why would it make a difference what order they get closed

[si_the_geek]

They are not independent of each other, they are parent/child objects - the Sheet is a child of the Book, which is a child of the App.

As such it's not like front/back/side doors, but more like turning off your computer and then trying to close one of the programs, and then trying to close the file that is open in that program.