cdo attach fails - file in use
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?
Re: cdo attach fails - file in use
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
Re: cdo attach fails - file in use
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:
Code:
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.
Re: cdo attach fails - file in use
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
Re: cdo attach fails - file in use
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.