Hanging Excel process

[Alc]

After following (or so I thought) step-by-step instructions I found on the forum for opening and closing Excel, I'm still being left with a hanging process.

This is the code (strImport file being a CSV file whose name and path I've set elsewhere)

 MsgBox "-1"
        Set Xl = New Excel.Application
MsgBox "0"
        Set XLBook = Xl.Workbooks.Open(strImportFile)
MsgBox "1"
         XLBook.SaveAs Replace(XLBook.FullName, ".csv", ".xlsm"), 52                             ' Save the CSV file as an Excel file
MsgBox "2"
        XLBook.Close
MsgBox "3"
        Set XLBook = Nothing
MsgBox "4"
        Xl.Quit
MsgBox "5"
        Set Xl = Nothing
MsgBox "6"

Following the progress,
- between -1 and 0 an Excel process starts
- I was hoping that by 6 it would have been stopped, but no luck.

 

[Gasman]

I think you would need to tell Excel the type of file it is

See here

http://codereview.stackexchange.com/questions/94984/converting-csv-files-to-excel-workbooks

Format:=6 ?

HTH

 

[Alc]

Thanks for the suggestion.

Code works as before, but still leaves the process hanging.

Addendum: Sorry, I should have clarified at the start, the code does exactly what it want it to, in terms of copying the file and saving as an XLSM file. The only problem is the hanging process.

 

[ByteMyzer]

You might try the following amended code:

MsgBox "-1"
    [COLOR="Navy"]Set[/COLOR] Xl = [COLOR="Navy"]New[/COLOR] Excel.Application
MsgBox "0"
    [COLOR="Navy"]Set[/COLOR] XLBook = Xl.Workbooks.Open(strImportFile)
MsgBox "1"
    XLBook.SaveAs Replace(XLBook.FullName, ".csv", ".xlsm"), 52 [COLOR="DarkGreen"]' Save the CSV file as an Excel file[/COLOR]
    XLBook.Saved = [COLOR="Navy"]True[/COLOR]
MsgBox "2"
    XLBook.Close
MsgBox "3"
    [COLOR="Navy"]Set[/COLOR] XLBook = [COLOR="Navy"]Nothing[/COLOR]
MsgBox "4"
    Xl.Quit
MsgBox "5"
    [COLOR="Navy"]Set[/COLOR] Xl = [COLOR="Navy"]Nothing[/COLOR]
MsgBox "6"

 

[Alc]

Once again, thanks for the suggestion but no change.

 

[Frothingslosh]

Out of curiosity, do you have warnings or error handling turned off? By everything I can see, you shouldn't be stuck with that hanging process.

 

[Gasman]

Once again, thanks for the suggestion but no change.

Does it even work interactively?
I had a situation where an excel xls file downloaded from a website, would error out when trying to open it. Interactively it produced a warning message and I found out I had to use an extra parameter to open it via vba.

 

[sneuberg]

I noticed that if you open the output file (.xlsm file) manually and close it that the process goes away. I' tried to do that in code but it's not killing the process.

Anyway if you didn't already know the hanging process seems to be associated with the output file.

 

[Alc]

Thanks, gentlemen. In order:

Frothingslosh: In the final version, I intend to turn the warnings off at the start and back on at the end, but for now I've left it as is, in the hope of identifying any problem lines. The message boxes let me see exactly where the process first shows up in Task Manager, so I know it's not being created elsewhere and I'm just missing it.

Gasman: Yes, all the code does exactly what I want it to, except for closing that damned process. Very perplexing.

sneuberg: Thanks, I'd come to the same conclusion but there I got stuck.



For the time being, it isn't preventing me from doing the further work I have to do on the form that runs this code, but I know it's likely to cause problems if I let it go 'live' as is.

 

[Alc]

I've had this happen a couple times. Once it was caused by what Gasman mentioned - Excel was trying to bring up a confirmation box and wasn't getting an answer - and once I never did track it down. That second time, however, I got rid of the problem by, of all things, skipping the Application declaration and just using Excel.Application.Workbooks.Open("yaddayadda") to open the workbook. Never figured out the cause, but the problem went away and that was enough for me.

Tried and no luck.

Knocking off time now, so will have to revisit it in the morning (when it will, no doubt, suddenly work).

Thanks for the help.

 

[sneuberg]

Good Morning Alc,

I played with this some more and I found that process only hangs when the output file already exists and you replace it. So the following code offers a workaround which I test and it works. It just deletes the file if the user wants that. This would need an error handler to check for errors the kill function can raise. But of course I hope a better solution can be found. I noticed that this problem doesn't occur for regular excel files, i.e., when tried with the comments out lines.

Dim Xl As Excel.Application
Dim XLBook As Excel.Workbook
Dim strImportFile As String
Dim strOutputFile As String

strImportFile = "C:\Users\sneuberg\Desktop\Events.csv"
Set Xl = New Excel.Application
Set XLBook = Xl.Workbooks.Open(strImportFile)
strOutputFile = Replace(XLBook.FullName, ".csv", ".xlsm")
If Len(Dir(strOutputFile)) <> 0 Then
    If MsgBox(strOutputFile & " already exists.  Do you want to replaced it.", vbYesNo) = vbNo Then
        Exit Sub
    End If
    Kill strOutputFile
End If
XLBook.SaveAs strOutputFile, xlOpenXMLWorkbookMacroEnabled  ' Save the CSV file as a Macro enabled. Excel file\
'strOutputFile = Replace(XLBook.FullName, ".csv", ".xls")
'XLBook.SaveAs strOutputFile, xlExcel12  ' Save the CSV file as a regular Excel file\
XLBook.Close
Set XLBook = Nothing
Xl.Quit
Set Xl = Nothing

 

[Alc]

Good Morning Alc,

I played with this some more and I found that process only hangs when the output file already exists and you replace it. So the following code offers a workaround which I test and it works. It just deletes the file if the user wants that. This would need an error handler to check for errors the kill function can raise. But of course I hope a better solution can be found. I noticed that this problem doesn't occur for regular excel files, i.e., when tried with the comments out lines.

Dim Xl As Excel.Application
Dim XLBook As Excel.Workbook
Dim strImportFile As String
Dim strOutputFile As String

strImportFile = Me!txtFileLocation
 Set Xl = New Excel.Application
Set XLBook = Xl.Workbooks.Open(strImportFile)
strOutputFile = Replace(XLBook.FullName, ".csv", ".xlsm")
If Len(Dir(strOutputFile)) <> 0 Then
    If MsgBox(strOutputFile & " already exists.  Do you want to replaced it.", vbYesNo) = vbNo Then
        Exit Sub
    End If
    Kill strOutputFile
End If
XLBook.SaveAs strOutputFile, xlOpenXMLWorkbookMacroEnabled  ' Save the CSV file as a Macro enabled. Excel file\
'strOutputFile = Replace(XLBook.FullName, ".csv", ".xls")
'XLBook.SaveAs strOutputFile, xlExcel12  ' Save the CSV file as a regular Excel file\
XLBook.Close
Set XLBook = Nothing
Xl.Quit
Set Xl = Nothing

Once again, thanks for taking the time on this.

I copied your code into the On Click event of a new button on my form, changing only the part where strImportFile gets set (as shown above).

Same damn result.

Even if I use the XLS as opposed to XLSM version (below), it leaves the hanging process. Starting to think Access just has it in for me.

 Private Sub Command25_Click()
Dim Xl As Excel.Application
Dim XLBook As Excel.Workbook
Dim strImportFile As String
Dim strOutputFile As String
 strImportFile = Me!txtFileLocation
Set Xl = New Excel.Application
Set XLBook = Xl.Workbooks.Open(strImportFile)
'strOutputFile = Replace(XLBook.FullName, ".csv", ".xlsm")
strOutputFile = Replace(XLBook.FullName, ".csv", ".xls")
If Len(Dir(strOutputFile)) <> 0 Then
    'If MsgBox(strOutputFile & " already exists.  Do you want to replaced it.", vbYesNo) = vbNo Then
    '    Exit Sub
    'End If
    Kill strOutputFile
End If
'XLBook.SaveAs strOutputFile, xlOpenXMLWorkbookMacroEnabled  ' Save the CSV file as a Macro enabled. Excel file\
 XLBook.SaveAs strOutputFile, xlExcel12  ' Save the CSV file as a regular Excel file\
XLBook.Close
Set XLBook = Nothing
Xl.Quit
Set Xl = Nothing
End Sub

 

[sneuberg]

I just retested this with both file types. I moved the code to a form so that my situation was identical to yours and it's not leaving a hanging process on my system which is Windows 7 and Access 2013. Since I can't replicate your symptoms I can't help any longer but I attached the database that I was using to test this. Maybe the other forum members can use this for testing.

 

[Alc]

I just retested this with both file types. I moved the code to a form so that my situation was identical to yours and it's not leaving a hanging process on my system which is Windows 7 and Access 2013. Since I can't replicate your symptoms I can't help any longer but I attached the database that I was using to test this. Maybe the other forum members can use this for testing.

Thanks again, anyway.

In case it makes a difference, I'm using Access 2010, Windows 7 SP1.

 

[Frothingslosh]

Out of curiosity, ALC, do you have ANY other references to Excel active anywhere else in your code? Do you have Excel already open when you run this?

I ran Sneuberg's code and got the same results as he did - no hangs at all. I'm on Windows 7/Access 2007 here.

In fact, what happens if you use the version in his database, rather than your current one? That would at least tell it if it's a problem with that particular project or something else.

 

[sneuberg]

This thread says, "Don't close the workbook first. Instead of closing the Workbook use Application.Quit after the save."

 

[Frothingslosh]

...

That may just do it.

 

[Alc]

Out of curiosity, ALC, do you have ANY other references to Excel active anywhere else in your code? Do you have Excel already open when you run this?

No, no other references to Excel and no other processes running.

I ran Sneuberg's code and got the same results as he did - no hangs at all. I'm on Windows 7/Access 2007 here.

I'm thinking Access 2010 may be the issue. 2007 worked fine for both of you and we're all running Windows 7.

In fact, what happens if you use the version in his database, rather than your current one? That would at least tell it if it's a problem with that particular project or something else.

Exactly the same thing happens! The code runs as expected BUT it leaves the Excel process hanging.

The only two differences I can see are:
1. I'm using Access 2010
2. I noticed was that Sneuberg had a reference to the Excel 15.0 Object Library, which I had to change to 14.0 to run it on my computer.

This thread says, "Don't close the workbook first. Instead of closing the Workbook use Application.Quit after the save."

Tried Xl.Quit, Excel.Application.Quit and (stupidly) Application.Quit - which both shut down Access AND left the Excel process hanging.

This is looking very much like a version problem to me.

 

[sneuberg]

One more thing that's different is the CVS file we are using for testing. I've attached the one I'm using. Could you upload the one you are using

 

[Alc]

One more thing that's different is the CVS file we are using for testing. I've attached the one I'm using. Could you upload the one you are using

Unfortunately, the file I'm using is full of customer data, so I can't upload it. I can, however, confirm that I get the same problem when I use your db AND your supplied file.