0

I developed a custom Visual Studio 2015 application where you can browse for excel files in your computer, then append the sheets from the first excel file to the second excel file.

The application works just fine when the first Excel file (the file whose sheets are copied to the second file) has more than one sheet. However, the application hangs when the first file only has one sheet that is trying to be added to the second file. The application works just fine regardless of the number of sheets in the second file.

Can anyone tell me why this is?

Should I use a different loop (e.g. Do Until)?

Here's a link to a photo of my application: Excel Report Merger:

Excel Report Merger

    Dim File1 As String = Trim(txtFileDirectory1.Text)
    Dim File2 As String = Trim(txtFileDirectory2.Text)

    'Opens files selected directories
    Dim xlApp As Excel.Application = New Excel.ApplicationClass
    Dim xlWorkBook1 As Object = xlApp.Workbooks.Open(File1)
    Dim xlWorkBook2 As Object = xlApp.Workbooks.Open(File2)
    Dim xlWS As Excel.Worksheet

    xlApp.Visible = False
    xlApp.DisplayAlerts = True

    'Append sheets from first report to second report
    Dim i As Integer = 1
    For Each xlWS In xlWorkBook1.worksheets
        xlWorkBook1.Worksheets(i).Copy(After:=xlWorkBook2.Sheets(xlWorkBook2.worksheets.count))
        i = i + 1
    Next xlWS

    'close first report
    xlWorkBook1.close

    'show second report with appended workbooks
    xlApp.Visible = True
Bennett Yeo
  • 819
  • 2
  • 14
  • 28
Jacob
  • 43
  • 7
  • You do not appear to be doing anything that requires you to use the Excel Interop instead of the [Open XML for Office SDK](https://msdn.microsoft.com/en-us/library/office/bb448854.aspx), you really should be using that instead of the interop – Scott Chamberlain Nov 18 '15 at 00:23
  • Don't forget to use the `Marshal.FinalReleaseComObject` for all excel objects if your going to use the Interop sdk. I agree with @ScottChamberlain on this one. – OneFineDay Nov 18 '15 at 01:36
  • What information does the screenshot of your application provide to your question? It's appearance is immaterial. As far as your code, what does the debugger show you when you step through it? – Ken White Nov 18 '15 at 02:37
  • @OneFineDay [No you should not](http://stackoverflow.com/questions/3937181/when-to-use-releasecomobject-vs-finalreleasecomobject/3938075#3938075), let the CLR do it's job, the only reson people keep seeing the objects stick around is because they are debugging and hitting stop instead of closing their programs preventing the GC from cleaning up the COM objects. – Scott Chamberlain Nov 18 '15 at 03:05
  • @ScottChamberlain - That is not true. That is a best practice for COM objects. The CLR does not clean those up - in fact it will leave the process open if you do not. It has been awhile since I have used them, but have much experience with that. – OneFineDay Nov 18 '15 at 03:14
  • Hi all, thank you very much for your responses. I woke up this morning and tested it again, and now it's working for some reason. I think Visual Studio 2015 debug is simply unstable at this point. – Jacob Nov 18 '15 at 15:30

0 Answers0