I have an application (running in IBM Notes, using Lotusscript etc.) that create a new Excel object via OLE automation. In my app I create a new Workbook and saving it to a temporary folder (I am saving it as Excel Worksheet w/o macros -> type 52) so that the resuting file is sth. like that:
tmp_abc1234.xlsx
Then I open the file in Excel programmatically. I have an Excel Addin (filename is "Teamoffice.xlam" loaded where some macros reside I will trigger in the custom save actionbar button. In this save method I also call the save function:
Call ActiveWorkbook.Save
Call ThisWorkbook.Save
For some reason Excel creates a file named "Teamoffice.xlsm" in the "My Documents" folder. The file itself cannot be opened in Excel ("wrong file format error") - of course, it's an Addin!
This works well if I have only one Excel document open from my application. If I open a second (or more) I run into trouble: everytime I save on of those secondary opened (or newly created) documents Excel asks me if I want to overwrite that "Teamoffice.xlsm" file.
I added VBA code to ma macro that detects if this file is there and tries to delete it. With no luck of course, as the first instance of Excel is locking that file already. Overwriting the file when aswering the Excel-question dialog works though, but I don't want that question to appear.
Excel doesn't behave like that when using it stand-alone w/o OLE.
Several questions resulting from this behavior:
- Why does Excel create this file? Can I stop it from doing so?
- How can I delete the file even if Excel has a handle on it or
- Can I tell Excel not tohave a handle on this file (if it's there) and let me kill it?
- Can I suppress the question dialog and always say "yes, overwrite it"?
Office version is 2010 - and it makes me going nuts... so thanks in advance! p.s.: I am not that VBA pro, so please be as detailed as you can effort :-)