locked
Pardon my bad code, but I can't get Excel to close. Take 3.

    Question

  • I have a little program that I wrote to translate an Excel spreadsheet into a text file and pass it to another application. We found out Friday that if you give it a spreadsheet ina bad format, theprogram just hangs up: no errors and no more processing. It hangs and never finishes processing. If the format is correct, no problems.

    I wanted to add error logic to catch this condition.  So after the try to open the spreadsheet, the first thing I do is check this one cell and if it is not right, it creates and error message, sets an indicator for the error,  and closes the workbook. It hangs up on the close of the workbook. Any ideas of why this could be true. A spreadsheet with the expected format closes just fine. I even copied the close statement up to follow the detection of the error, but it hangs just like it did at a different point in the program.

    I have been trying to copy the code here, but the question ends up either blank or doesn't get posted at all.

    The workbook is defined by oWB=oXL.Workbooks.Open("Excel.Application"). oWB.Close() is what hangs. That same code does not hang if the format is correct. How can the contents of the file affect the closing of it?

    Tthanks for any ideas!

    B


    BGCSOCAL
    Monday, July 25, 2011 3:49 PM

All replies

  • Hello bgclB,

    Thanks for your post.

    Could you please provide the complete code on your side? If so, we could reproduce the issue on our side. By the way, below sample describes how to export excel data into text file. It provides two solutions. The first is using Excel.Application object. You could try the sample code and let us know the situation on your side. The second is using using OleDB to read Excel file and insert first into dataset and later writing into text file.

    http://codeproject.wordpress.com/2007/09/25/vbnet-how-to-export-excel-data-into-text-file/
    (VB.NET – How to export excel data into text file)

    If you have any concerns, please feel free to follow up.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 29, 2011 5:44 AM
  • The program extensively uses the excel.applicaiton object and at this point, re-writing it is pretty muych a non-starter. As far as putting the entire code here:

    1. the program is quite large. IS there a way to attach a file?

    2. When I tried to copy just that little bit I showed, the question text goes completely blank or it won't post the message. Hence the Take 3 on the subject line. I gave up on copying the text after those.  Again, if there was a way to attach a file, that would work much better.

     

    Why the close is failing at this point I ahve no idea. If the file is in the correct format, it does not fail.  I copied the close logic up to this place to see if it made any difference and it dowsn't. IF the file is the wrong format, the close hangs whether at the original location or as copied to this point. By wrong format I mean the the internal data is formated wrong. There is nothing wrong with the Excel file structure.

     

    As far as exporting I can't do that. If that was possible I ahve a utility that could do that in a blink.  I generate the text file and pass it to another program.  Myprogram is accessing the spreadsheet and a database where information is converted to the values the receiving software expects.


    BGCSOCAL
    Friday, July 29, 2011 5:31 PM
  • From the sounds of it you are hitting a loop some place... Try adding some breakpoints to see how far into your code you are getting and perhaps you can narrow down where the issue is.
    Friday, July 29, 2011 7:21 PM
  • I di trace it down and it is not caught in a loop. The main porgram does some housekeeping and opens the database. IT then call the reoutine in question. The routine opens the Excel file and checkss the format of the data. IF not good, it closes the excel file. That is where it jhangs. It has not gotten in a loop yet.
    BGCSOCAL
    Friday, July 29, 2011 7:38 PM
  • Use your Skydrive to post files for other forum  members.  A link to it make it easy for others to find.


    Saturday, July 30, 2011 3:44 PM
  • When you say hangs, do you mean excel never closes or it closes and your code doesn't proceed?
    Monday, August 01, 2011 10:38 AM
  • What's a skydrive?
    BGCSOCAL
    Monday, August 01, 2011 2:48 PM
  • The excel does not close and the code does not proceed.
    BGCSOCAL
    Monday, August 01, 2011 2:49 PM
  • Ok, not to beat a dead horse but you have put in a break point on the line that you have that closes excel?  Or are you using the same sub/function/class to do your closing of excel?  From the sounds of it your application is stuck some place in the failed processing of the file and doesn't know that it should continue on.  It's been a long time since I done anything with Excel, but when I was working on a Word project I had to use something like:
    Try 'close word if it's open
          wrdApp.Application.Quit()
        Catch ex As Exception
          'Already closed
        End Try
    
    I believe .Close just closes the "current" workbook, but I could be wrong.
    Monday, August 01, 2011 3:26 PM
  • What's a skydrive?
    BGCSOCAL

    Click on it at the top of your Windows Live page.
    Monday, August 01, 2011 4:49 PM
  • I used the try statement with my original close and that just hung up like before.  I replaced the close of the workbook with the quit statement as you suggested. It did not hang up, but it did not close Excel either.  I need that to happen. This little program takes stuff all day long and I can't afford a bunch of excel's hanging out there.


    BGCSOCAL
    Monday, August 01, 2011 5:30 PM
  • File is on skydrive. Problem starts at 182. IS in folder marked VS 2005. IS that all you need to get to it?
    BGCSOCAL
    Monday, August 01, 2011 5:36 PM
  • just for reference:
    Dim wrdApp As New Word.Application
    

    Another funky thing I found with using Word, was that stupid little assistant can cause you lots of problems if it's up as far as closing goes.

    Monday, August 01, 2011 5:40 PM
  • Hi bgcLB,

    Could you please share the link of the folder on the skydrive? If not, we could not check.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 03, 2011 10:05 AM
  • this post of temlehdrol reminds me a thing: it is possible that Excel want you respond to a messagebox, but you have set visible false and you can't respond. so all hangs.

    I don't remember surely, but there should be a setting to have Excel not to show these dialog boxes.


    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Wednesday, August 03, 2011 5:14 PM
  • this post of temlehdrol reminds me a thing: it is possible that Excel want you respond to a messagebox, but you have set visible false and you can't respond. so all hangs.

    I don't remember surely, but there should be a setting to have Excel not to show these dialog boxes.



    Actually this reminds me of something, when you say bad format do you mean one that Excel can't read?  If so then this would be correct, Excel throws up a messagebox at some point during the load, saying it can't read the file (or something along those lines).  We deal with a lot of hospitals and the like at work and most of them are years behind so our main version of office is 2000, however every so often we get a "new file" and I have to use my local computer to open it and convert it.  When we get one of those files you get a message box saying that Excel can't open the file or recogonize the format (something like that).  So long story short this may be what is causing the "error".

    What happens when you try to open the file without your application?

    • Edited by temlehdrol Wednesday, August 03, 2011 5:43 PM added question at the end.
    Wednesday, August 03, 2011 5:41 PM
  • No, the error message ends up in a text file, not Excel. It never gets past the hanging up to get to the write to the text file.
    BGCSOCAL
    Wednesday, August 03, 2011 6:50 PM
  • No the Excel format is correct and readable by  2007 or 2010. The bad format referes to the format of the rows and columns inside the spreadsheet. Just data that is not in the right format for this program to process, but is valid for Excel.
    BGCSOCAL
    Wednesday, August 03, 2011 6:52 PM
  • I'm not able to see your files on skydrive (never used it so it could be me) so bear with me here.  I'm going to give a simple example and you can explain if I'm wrong.

    Lets say you have the following data:

    A1 = abcdefg-123   B1 = Jon Smith C1 = 8/3/2011

    A2 = hijklmn-456  B2 = Joe Camel C2 = August 23, 2011

    So lets say A and B are good, and C should be in M/D/Y numerical format.  When you do row 1, you'll be ok but when you do row 2 you'll hit a error (or should hit) when you get to C.

    Is this what you mean by wrong format (again just a simple example).  When this is happening if you Alt + Tab are there any "hidden" message boxes as M$ is famous for not having them in the foreground.  Are you able to step through your code until you hit the bad spot?

    Wednesday, August 03, 2011 7:18 PM
  • I never used sky drive before either so maybe that is not the correct URL.

     

    By bad format I mean something like:

    a1 'Event Viewer'  b1 blank c1 blank

    vs a1 'Invoice # '  b1 <the number of the invoice> c1 <number of deatial lines in the invoice>


    BGCSOCAL
    Wednesday, August 03, 2011 7:25 PM
  • This would be so much easier if I could see your code... I'm guessing you are reading your spreadsheet using a loop?  

    I think I understand how your app works but I want to be sure.

    Read Row 1 from Excel File, write that data after doing something to it to a text file
    Read Row 2 from Excel File, write that data after doing something to it to a text file 
    etc... 

    Wednesday, August 03, 2011 7:39 PM
  • Not really. It is more complicated. However, at the point I'm checking and get hung up trying to close, I'm on the first row and have discovered that it says Event Viewer so I want to stop now. The looping part hasn't started yet.
    BGCSOCAL
    Wednesday, August 03, 2011 7:44 PM
  • I never used sky drive before either so maybe that is not the correct URL.

     

    Was just looking at sky drive a bit more, did you set the share to allow others to view the folder/files?
    Wednesday, August 03, 2011 7:46 PM
  • Who are you sharing your files with on your Skydrive?  If you share with Everyone (public), then everyone can see them.
    Wednesday, August 03, 2011 7:47 PM
  • Ok, I'm not sure how picky Excel is, but have you closed what ever connection you are using to connect to Excel before trying to Exit out of Excel?
    Wednesday, August 03, 2011 7:48 PM
  • You and John Wein hit it. I jsut shared it with everyone. Sorry. First time I ever used it.
    BGCSOCAL
    Wednesday, August 03, 2011 7:57 PM
  • Ok this is strictly a preference thing (and nothing to do with your question), but get rid of the GoTo statements, it makes code a pain to follow and if you ever have to go back to this you'll thank yourself for not using them. Instead of GoTo PF_Out (for example) you could just as easily use Exit Sub and that's a lot more descriptive.  But again that's a preference thing....

    Anyways, so the line you are hitting and not getting past is: Row2Col1 = oSheet.Cells(2, 1).value.ToString

    Put a break point on 181 and step into the IF, while verifying the value of Row2Col1.  You don't need the Try in there by the way, I appreciate using the example I gave you, but you really shouldn't get that far in your code if Excel is already closed/not open.  In my project there was a chance word would already be closed thus the reason for the Try block.  Though if you think you are getting a error while trying to close excel, by all means keep it there and report the exception..

     

    Also on another side note I'll give you the same lecture I got, Modules are so VB6, the latest and greatest is to use Classes.  Ok I'm paraphrasing a lot here, but just thought I'd pass that along.  Though at this point I'd just leave it as there is no point in re-doing 1000 lines of code, but something to think about with future projects.

    Thursday, August 04, 2011 1:59 AM
  • No it is hanging up at oWB.close. Row2col1 gets the correct value and the IF valuation works correctly but it hangs when it tries to close the worksheet.

     

    Exit sub sounds good.

     

    I leaned VB6 out of a book and have been winging it ever since. I keep hopinig  my last program is my last program but it never seems to turn out that way. Got a good suggestion on where to read up on classes and programming with them?


    BGCSOCAL
    Thursday, August 04, 2011 2:25 PM
  • Every book I have on classes really isn't that great, but if you want a basic understanding the Wrox Press Books usually do a decent job.  I've yet to find a book that goes into a lot of detail, but they give you and example of how to have some functions and return values... Otherwise just look up 'vb.net Classes' on Google and see what you can find.

    If you use just oXl.Quit() does that work?  Haven't tested it, so give it a shot.

    Thursday, August 04, 2011 2:46 PM
  • Yes I tried the .quit. The program did not hang up, but excel was left running and had to be killed via task manager. No good. This little program runs 24X7 and would run out of resources real soon.
    BGCSOCAL
    Thursday, August 04, 2011 2:49 PM
  • Well this is good news... do you actually need to close Excel or just the bad workbook?
    Thursday, August 04, 2011 2:50 PM
  • Both really; otherwise the spreadsheet stays locked and does not move out of the processing area so that the next file gets moved in.
    BGCSOCAL
    Thursday, August 04, 2011 2:57 PM
  • Ok, this is the closest I can find without searching through a whole bunch of projects that deals with office (Word In this example)
    If p.Count = 0 Then ' Word is not open to open it
          oWord = CreateObject("Word.Application")
          Try
            oDoc = oWord.Documents.Open(strAttachment)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oDoc)
          Catch ex As COMException 'Some kind of error, perhaps hit cancel on the password question
            oWord.Application.Quit()
            If ex.ErrorCode = -2146822880 Then
              MessageBox.Show("Incorrect Password", "Oops...", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ElseIf ex.ErrorCode = -2146824090 Then
              'do nothing, person probably hit cancel when asked for a password
              'Exception thrown by object to handle exiting from the password request window
              'MessageBox.Show("Command Failed Error Message Received" & vbCrLf & ex.ToString(), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
              MessageBox.Show(ex.ToString(), "Word Not Open?")
            End If
    
          End Try
        Else 'word is already open, or at least Windows thinks it's open
          Try
            oWord = Marshal.GetActiveObject("Word.Application")
            oDoc = oWord.Documents.Open(strAttachment)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oDoc)
          Catch ex As COMException 'Some kind of error, perhaps hit cancel on the password question
            If ex.ErrorCode = -2146822880 Then
              MessageBox.Show("Incorrect Password", "Oops...", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ElseIf ex.ErrorCode = -2146824090 Then
              'do nothing, person probably hit cancel when asked for a password
              'Exception thrown by object to handle exiting from the password request window
              'MessageBox.Show("Command Failed Error Message Received" & vbCrLf & ex.ToString(), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ElseIf ex.ErrorCode = -2147221021 Then
              'Operation unavailable (word is not actually open, but it thinks it is)
              'Hard to test for as not very common and no pattern found as of yet 4/27/11
              MessageBox.Show("Word is acting up, wait a few seconds and try again" & vbCrLf & "Most likely due to the 'Office Assistant'", "Word Issue", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
              MessageBox.Show(ex.ToString(), "Word Open?")
            End If
            'oWord.Application.Quit()
          End Try
        End If
    
    This is a great example of why comments are good... I know I needed the line System.Runtime.InteropServices.Marshal.ReleaseComObject(oDoc) for some reason but I can't remember the exact reason. Not sure if this will be of any help, but I can tell you I really hate programming for M$ Office :-)
    Thursday, August 04, 2011 5:37 PM
  • Releasing a comm object that you just opened doesn't make sense to me, but I inserted the line with necessary revisions and it amde no difference with the application quit logic. The file was still open and excel was still running. When I tried with the oWB.close() it did not hang up, it got stuck in a loop trying to process the temp file over and over again. By temp file I mean the one with the tilde in front. Don't know what it is really called.
    BGCSOCAL
    Thursday, August 04, 2011 5:53 PM
  • Like I said, that is a great example of why it's good to comment, I remember I had to do it for something.  Was hoping it was because the stupid application wouldn't close without it, but no such luck apparently.

    What is the value of your excelObject when you call the .Quit, the name should be something along the lines of 'Microsoft Excel'

    Thursday, August 04, 2011 6:08 PM
  • ok I just made the simplest program I could think of with excel...

    Imports Excel
    
    Module Module1
      Public oXL As Application
      Public oWB As Workbook
      Public oSheet As Worksheet
      Sub Main()
    
        Console.WriteLine("Hit any key to start the Excel Test")
        Console.ReadKey()
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True
        Try
          oWB = oXL.Workbooks.Open("Test", , False)
          oSheet = oXL.ActiveSheet
        Catch ex As Exception
          Console.WriteLine(ex.ToString())
        End Try
        Console.WriteLine("Closing Excel")
        Console.ReadKey()
        oXL.Application.Quit()
        Console.WriteLine("Is it closed?")
        Console.ReadKey()
      End Sub
    
    End Module
    
    

    I have some bad news, this works "fine"... granted it doesn't to anything more than start Excel, fail at opening the file (doesn't exist) and then closes Excel.  I also changed it so it would open a file, and it still closes excel (granted I'm using 2000 here at work (don't get me started on that)).

    Thursday, August 04, 2011 6:20 PM
  • I do a workbook close becuase that works. If I comment out the test for invalid format and feed this program a valid spreadsheet, it works perfectlly with the oWB.close().  Perfectly. Even with the code uncommented, if the data in the file is correctly formatted, the program works fine using the oWB.close() construct. If I get an error (which I did because I forgot to test for a null value before the assignment to row2col1, it closes well with the owb.close().

     

    If I put in the oXL.application.quit() the program completes, but Excel is left hanging out there and the file is still locked. I just tested again after the inclusion of the null value check.

     

    This is why it makes no sense to me. Written my way it works fine as long as I don't detect a bad format. The original version without the check is currently working well in production as long as the format is OK. Your program written your way works fine. Does that make any sense?


    BGCSOCAL
    Thursday, August 04, 2011 9:05 PM
  • Nope, makes no sense at all... I even tried:


    Sub Main()

            Console.WriteLine("Hit any key to start the Excel Test")
            Console.ReadKey()
            oXL = CreateObject("Excel.Application")
            oXL.Visible = True
            Try
                oWB = oXL.Workbooks.Open("C:\test\test.xls", , False)
                oSheet = oXL.ActiveSheet
                'Test that excel format is correct.
                Row2Col1 = oSheet.Cells(2, 1).value.ToString
                If Row2Col1 = "Event Number" Then
                    sOutputLine = "Accounting File is in wrong format."
                    indError = True
                    Try
                        oXL.Application.Quit()
                        'oWB.Close()
                    Catch ex As Exception
                        Console.WriteLine(ex.ToString())
                    End Try
                    indExcel = False
                    Exit Try
                End If
            Catch ex As Exception
                Console.WriteLine(ex.ToString())
            End Try
            Console.WriteLine("Closing Excel")
            Console.ReadKey()
            Try
                oXL.Application.Quit()
                'oWB.Close()
            Catch ex As Exception
                Console.WriteLine(ex.ToString())
            End Try
            Console.WriteLine("Is it closed?")
            Console.ReadKey()
        End Sub
    made the file and added that to the cell and it still works fine.  However now when I try to open any excel workbook by double clicking on the icon Excel opens but doesn't display anything.  I have to open Excel and then use file open to open anything.  I did mention I hate programming for Office Products right :-)

     


    Friday, August 05, 2011 10:52 AM
  • Actually I was just looking at my code and I see that I should be getting an error... It's trying to close Excel twice (in the case of an error in format), so on the second time through (the closing) it should throw an error as it's already closed.  I stepped through the code and it does hit both closes and no error which is odd.  I checked the task manager and after the first close it is gone...

    Well I'm out of ideas as to why it's not working for you.  If you don't have any more luck here try the Excel for Developers forum... http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    Monday, August 08, 2011 11:38 AM
  • I really appreciate all of your help. I think the forum is a good idea.

     

    Thanks

    B


    BGCSOCAL
    Monday, August 08, 2011 4:45 PM
  • You may want to reference this thread in your new one, to give them an idea.  Perhaps someone that deals with Excel on a regular basis will be of more help.  Good luck!
    Monday, August 08, 2011 4:46 PM