Have you tried running either this or Sneuberg's code on a different machine? Doesn't have to be a different version of Office (in fact, for this test, it's best if it's the same version of Office), just a different machine.
Have you tried running either this or Sneuberg's code on a different machine? Doesn't have to be a different version of Office (in fact, for this test, it's best if it's the same version of Office), just a different machine.
How do you determine that Excel is hanging?
What happen if you comment out the Save as code line?
I've tried sneuberg code without any problem, except from setting the correct Excel object reference.
My setup is: Windows 8, MS-Access 2010, Excel 2000.
Whenever I ran into that problem, I would notice it by trying to open the new spreadsheet, only for Excel to refuse because I already had it open. (One fix was to open ANOTHER spreadsheet, and both would appear on my screen. Another was to exit Access entirely, which would end the Excel process at the same time.) I would expect that you can also see Excel.exe in the active processes, as well.
One of these days, work is going to allow me to stay on a problem until it's fixed, instead of having to move around and leave half a dozen unfinished.
Back to this issue.
Have you tried running either this or Sneuberg's code on a different machine? Doesn't have to be a different version of Office (in fact, for this test, it's best if it's the same version of Office), just a different machine.
After a long time on something else, I was able to get back to this problem and found a solution. I'm posting it in the hope it'll save someone else the same frustration.
This version leaves an Excel process hanging
Code:
Set Xl = New Excel.Application
Set XLBook = Xl.Workbooks.Open(strImportFile)
XLBook.SaveAs Replace(XLBook.FullName, ".csv", ".xlsm"), 52
XLBook.Close
Set XLBook = Nothing
Xl.Quit
Set Xl = Nothing
This version leaves no Excel processes hanging
Code:
set XLBook = CreateObject("Excel.Application").Workbooks.Open(strImportFile)
XLBook.SaveAs Replace(XLBook.FullName, ".csv", ".xlsm"), 52
XLBook.Close
Set XLBook = Nothing
Out of curiosity, can you tell if this closes Excel immediately or does it wait until Access also closes?
I'm asking because this is relevant to a discussion in another thread about cleaning up after one's self. According to the theory in that other thread, if you didn't do either of the last two steps on XLBook (.Close and set to Nothing), Excel should go away anyway because of "process rundown." I have been skeptical regarding whether that is actually true and this seems like an ideal test case.
Didn't really have time to look at all the thread. Looks like it was solved.
The takeaway is to make sure any Excel method is qualified with the object reference, even if it is part of the parameters.
As I suspected the REPLACE is an Excel method.
It is not preceded with the Excel object (dot)
Let me propose that the test on a 2nd machine worked the first time, but was not tested the second time where it should error.
I wrote about this years ago on the forum.
I referenced it as an Orphan Excel process that is left running.
Very often, the code can run the first time, then fail the 2nd time due to the orphan process. Something to keep an eye out for when using Excel automation.
Open Task Manager
Single step through the Excel automation code.
The first instance of Excel should appear.
When finishing, if there is a 2nd instance of Excel (down in Task manage's details) that is what I reference as an orphan. It no longer has a pointer from the application code.
Within any call to a method, all arguments that reference Excel must be have the <excel object reference>.Excel Method
On the Excel part of this forum, I am likely to post Excel automation that runs from MSAccess.
While using the
With ExcelObject
.code
.code
End With
is much faster to execute, it is harder to step through and troubleshoot than
fully qualifying each line.
To verify that Excel closes, when single stepping through code use:
ObjXL.visible = true
where ObjXL is the object variable set to Excel.Application
But, always use Task Manager afterwards too!
It has been my experience with Office 2007 that if you open and use Excel via VBA but fail to not only close Excel but to also set the variable to nothing, you wind up with an Excel process remaining open.
If you don't close it because you have it visible for the user to continue working with, it's not a big issue, but if you're trying to do a background export, they can run into extreme difficulty accessing the file, because it's already open but hidden. (The workaround is to open a DIFFERENT spreadsheet in its own Excel process, then 'execute' the offending spreadsheet. At that point, it will appear and you can modify it, save it, and shut Excel down for real.)
And I specifically include situations where you closed Excel but did NOT set the variable to nothing. When I run into that situation and that problem, explicitly setting the variable to nothing always fixes the problem.
I offer my thanks to Rx and Frothingslosh for their observations. It has always been my position that failure to clean up objects will lead to dangling objects. Galaxiom called me down on this because ChrisO took the other position, that it wasn't necessary, and Greg even referred to a time when ChrisO posted the results of a test for this phenomenon. I've ALWAYS said that it does not hurt and often helps to clean up after one's self if there is even a CHANCE that you could have a "dangler" anywhere.
My position remains as it was. With no disrespect to ChrisO, I believe that cleanup is not only a good thing but a necessary thing when dealing with external application objects that can potentially take on a life of their own.
Thanks for the complement.
Just to add a little more: When using visible = true
The code should close the workbook and even leave the Excel frame (empty).
Then use the Excel.Quit (see code below)
This is code out of the project in front of me
Note: I use ObjXL as the object set to Excel.Application
Also note - the network response here is so slow, the double "save" with a DoEvents stopped the creation of those pesky Excel temp files every time a file was saved.
It is just my preference to later Set ObjXL = Nothing
Like Chicken Soup for a cold.... it couldn't hurt.
For the last few months (and years), I have created dozens of very complex Excel workbooks from code in Access. They create multiple tabs, populate data from SQL Server, and create complex dashboards with other features such as custom sort-grouping tree directories.
Not uncommon to have a couple of dozen pages of Excel automation code per project.
I can't think of a version that I have not used since '97
For those who accuse me of being REALLY OLD, that was 1997, not 1897 LOL
I was a MS MCT with a rare certification in Excel Object Model Programming and traveled for six years, six months and six days (it is written on my forehead so I don't forget... never ever do that much travel again!) I can't say this problem applies to any one version or another.
The works first time, fails the 2nd time is really tricky. Programmers don't expect it. (note, the 3rd time it works too, keep testing and end up with a dozen orphaned Excels in Task Manager).
But, I am always open to new suggestions....
Code:
ObjXL.Range("A1").Select
xlWB.Save
DoEvents
xlWB.Close
' ********************** Save and close ************************************************************************
xlWB.Save
DoEvents
xlWB.Close
' *************************************************************************************************************
'' Closing Objects Statements
End With
ObjXL.Quit ' <---- IMPORTANT **************** Close the empty Workbook - close the object model instance **************
Set rs = Nothing ' Not needed - but couldn't hurt! LOL
Set rs2 = Nothing
Set DB = Nothing
Set DB2 = Nothing
Set xlWB = Nothing
Set ObjXL = Nothing
We knew that. The earliest date you can express via Access or Excel is either 31-Dec-1899 or 1-Jan-1900 depending on which one you are using. So we knew it wasn't 1897. But that's OK, I remember working in Borland Paradox LONG before Access97 came out. In fact, I even worked with DEC Datatrieve, which was a non-PC database from a time before all of the modern SQL players came on the scene. Rx_, I'm probably up there with you.
Of course, neither of us is older than dirt. But we and dirt ARE on a first-name basis!
Out of curiosity, can you tell if this closes Excel immediately or does it wait until Access also closes?
I'm asking because this is relevant to a discussion in another thread about cleaning up after one's self. According to the theory in that other thread, if you didn't do either of the last two steps on XLBook (.Close and set to Nothing), Excel should go away anyway because of "process rundown." I have been skeptical regarding whether that is actually true and this seems like an ideal test case.
The second version of the code closes Excel immediately. I know this as I had the database open on one monitor and task manager open on another, connected monitor, so I could watch both at the same time.
I tried using the original 'problematic' code and just leaving everything alone afterwards. Four hours later, the Excel process was still open. Should 'process rundown' be left for longer than that?
That zombie process is not the fault of a long process rundown. Opening an application object such as Excel, Word, or Outlook creates a second process that has independent rundown management because it has its own task number or process ID. Thus my earlier comments about "objects that can take on a life of their own."
Once the "driver" of the overall application exits, the connection that controls the secondary process is gone - but since it was opened via automation, it apparently wasn't expecting any GUI-based command inputs anyway. Therefore, to me it is no surprise that the dangler dangled that long. On UNIX and OpenVMS, dangling tasks spawned by but not closed by another application often had to be manually killed or, even worse if it was expecting a specific type of command connection, killed by a reboot. This is not a Windows-only problem.
Using Task Manager and HIGHLY dependent on Windows version, you might be able to do a "Switch To" for that dangling copy and assert control over it. But you also might not be able to do that. Which is why T.M.'s "End Task" is such a good thing.
