Why does Excel keep opening in read-only mode (the 2nd time around) ???

[robbie]

I have this procedure where I am trying to refresh pivot tables in a workbook, save it, close it, and then attach it in an e-mail, then, open the workbook again and do the process all over again but when the workbook opens up the 2nd time through the loop, it always opens up as read-only. What am I missing or forgetting to do?

            ' ------------ '
            ' loop reports '
            ' ------------ '
            For X As Integer = 1 To rrec

                ' -------------------------------- '
                ' assign globals to current record '
                ' -------------------------------- '
                gReport_ID = R(X).Report_ID
                gReport_Name = R(X).Report_Name
                gReport_Header = R(X).Report_Header
                gEmail_Subject = R(X).Email_Subject
                gEmail_To = R(X).Email_To
                gEmail_From = R(X).Email_From
                gEmail_CC = R(X).Email_CC
                gEmail_BCC = R(X).Email_BCC
                gEmail_Body = R(X).Email_Body

                ' ------------------------- '
                ' update the report pointer '
                ' ------------------------- '
                UpdateReportPointer(X)

                ' ----------------------------------- '
                ' create the excel application object '
                ' ----------------------------------- '
                Dim oApp As Object = CreateObject("Excel.Application")

                Try

                    ' ------------------------------- '
                    ' this is the excel refresh piece '
                    ' ------------------------------- '
                    With oApp

                        ' ----------------------------------------------------- '
                        ' turn these properties to false to speed up processing '
                        ' ----------------------------------------------------- '
                        .Visible = True
                        .DisplayAlerts = False
                        '.Interactive = False
                        '.ScreenUpdating = False

                        ' ----------------------- '
                        ' open the excel workbook '
                        ' ----------------------- '
                        .Workbooks.Open(Excel_File_Path, , False)

                        '''''''''''''''''''''''
                        ' make RptHdr visible '
                        '''''''''''''''''''''''
                        .Sheets("RptHdr").Visible = True

                        '''''''''''''''''''''''''''''''''
                        ' activate the RptHdr worksheet '
                        '''''''''''''''''''''''''''''''''
                        .Sheets("RptHdr").Activate()

                        ''''''''''''''''''''''''''
                        ' point to starting cell '
                        ''''''''''''''''''''''''''
                        .Range("Rpt_Starting_Cell_Hdr").Select()

                        '''''''''''''''''
                        ' refresh query '
                        '''''''''''''''''
                        .Selection.ListObject.QueryTable.Refresh(False)

                        '''''''''''''''''''''''''''''''''
                        ' activate the Report worksheet '
                        '''''''''''''''''''''''''''''''''
                        .Sheets("Report").Activate()

                        ''''''''''''''''''''''''''
                        ' point to starting cell '
                        ''''''''''''''''''''''''''
                        .Range("Rpt_Starting_Cell").Select()

                        '''''''''''''''''
                        ' refresh query '
                        '''''''''''''''''
                        .Selection.ListObject.QueryTable.Refresh(False)

                        ''''''''''''''''''''''''''''''''''''''''''''''
                        ' point to the 1st detail cell of the report '
                        ''''''''''''''''''''''''''''''''''''''''''''''
                        .Range("Detail_Cell").Select()

                        '''''''''''''''''''''''''''''''
                        ' point back to starting cell '
                        '''''''''''''''''''''''''''''''
                        .Range("Rpt_Starting_Cell").Select()

                        '''''''''''''''''''''''''
                        ' make RptHdr invisible '
                        '''''''''''''''''''''''''
                        .Sheets("RptHdr").Visible = False

                        '''''''''''''''''''''''''''''''
                        ' save and close the workbook '
                        '''''''''''''''''''''''''''''''
                        .ActiveWorkbook.SaveAs(Excel_File_Path)
                        .ActiveWorkbook.Close()

                        '''''''''''''''''''''''''''''''''''''''''''''''''
                        ' turn these properties to true before quitting '
                        '''''''''''''''''''''''''''''''''''''''''''''''''
                        With oApp
                            .Visible = True
                            .DisplayAlerts = True
                            .Interactive = True
                            .ScreenUpdating = True
                        End With

                        ' --------------------------- '
                        ' quit the application object '
                        ' --------------------------- '
                        oApp.Quit()

                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                        ' destroy excel object twice to make sure resources have been released '
                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                        oApp = Nothing
                        oApp = Nothing

                        ' --------------------------------------------------------------------------------- '
                        ' garbage collection needs to be called twice in order to get the Finalizers called '
                        ' the first time in, it simply makes a list of what is to be finalized,             '
                        ' the second time in, it actually does the finalizing.                              '
                        ' Only then will the object do its automatic ReleaseComObject.                      '
                        ' --------------------------------------------------------------------------------- '
                        GC.Collect()
                        GC.WaitForPendingFinalizers()

                        GC.Collect()
                        GC.WaitForPendingFinalizers()

                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                        ' destroy excel object twice to make sure resources have been released '
                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                        oApp = Nothing
                        oApp = Nothing

                        ' ------------------------------------------------------------------- '
                        ' pause app to pause for 10 seconds after we destroy the excel object '
                        ' ------------------------------------------------------------------- '
                        Thread.Sleep(10000)

                        '''''''''''''
                        ' send mail '
                        '''''''''''''
                        Dim SendAttempts As Integer = 0

                        ''''''''''''''''''''''''''''''''''''''''''''''''
                        ' make 3 attempts to send mail before skipping '
                        ''''''''''''''''''''''''''''''''''''''''''''''''
                        Do
                            SendAttempts += 1
                            R(X).Email_Success = SendMail(X, SendAttempts)
                        Loop Until R(X).Email_Success = "Success" Or SendAttempts > 2

                        ' ----------------------------- '
                        ' update report type on success '
                        ' ----------------------------- '
                        If R(X).Email_Success = "Success" Then
                            UpdateReportType()
                        End If

                        '''''''''''''
                        ' log email '
                        '''''''''''''
                        AddToReportLog(X)

                    End With

                Catch ex As Exception

                    ' --------------------------- '
                    ' quit the application object '
                    ' --------------------------- '
                    oApp.Quit()

                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    ' destroy excel object twice to make sure resources have been released '
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    oApp = Nothing
                    oApp = Nothing

                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    ' garbage collection needs to be called twice in order to get the Finalizers called '
                    ' the first time in, it simply makes a list of what is to be finalized,             '
                    ' the second time in, it actually does the finalizing.                              '
                    ' Only then will the object do its automatic ReleaseComObject.                      '
                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    GC.Collect()
                    GC.WaitForPendingFinalizers()

                    GC.Collect()
                    GC.WaitForPendingFinalizers()

                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    ' destroy excel object twice to make sure resources have been released '
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    oApp = Nothing
                    oApp = Nothing

                    blnExcelError = True
                    strExcelError = ex.ToString

                End Try

                If blnExcelError = True Then
                    Throw New Exception(strExcelError)
                End If

            Next X

[kevininstructor]

Whether using early or late-binding (yours is late binding) you need to understand how the Excel automation model works and then there would be no need for calling any GC methods or waiting x amount of seconds in your for/next statement.

I have a sample project on MSDN that goes over properly creating Excel via automation and accessing properties and methods so that when finished nothing to do with Excel is left in memory w/o calling GC. The code is all early binding but if you want to stay with late-binding following along with what is shown will still work.

Any ways usually Excel opens in read-only mode when someone else has it open, open Task Manager to processes and look for Excel. Also another test is attempt opening the file while the app is open (set a break-point before the pause) then try and open via Windows Explorer. If you cannot the problem goes back to not properly working the objects you are using and need to be revamped as per how I show working with Excel in my MSDN article.

[robbie]

Kevin,

Thanks for your help.
I am looking at your example now.

[kevininstructor]

Here is a good example that if you have issues working thru my project. What the follow code will do is open the file, modify it, pause via MessageBox which at this time go open the file in Explorer and examine the values, close the file, back in the app press OK, next time the dialog appears press OK. Open the file in explorer and note one value is different (well actually everything is different as per the Dictionary used). Nothing will pop up indicating the file is read-only and if it does there is something else going on as I fully tested this before suggestion it.

Place a new button on the main for with the following code for the click event, change the following line in regards to path and file name OpenExcelWriteData2("C:\Dotnet2010\MSDN\Excel\Basics_1\bin\Debug\KSG1.xlsx", "Sheet1", D)

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim MyDicts As New List(Of Dictionary(Of String, String)) From
            {
                New Dictionary(Of String, String) From
                {
                    {"A1", "Month"},
                    {"A2", "January"},
                    {"A3", "February"},
                    {"A4", "March"},
                    {"A5", "April"},
                    {"B1", "Money Spent"},
                    {"B2", "1000.00"},
                    {"B3", "1500.00"},
                    {"B4", "1200.00"},
                    {"B5", "1100.00"}
                },
                New Dictionary(Of String, String) From
                {
                    {"A1", "Month"},
                    {"A2", "January"},
                    {"A3", "February"},
                    {"A4", "March"},
                    {"A5", "April"},
                    {"B1", "Money Spent"},
                    {"B2", "1000.00"},
                    {"B3", "1500.00"},
                    {"B4", "1200.00"},
                    {"B5", "555.00"}
                }
            }
        For Each D In MyDicts
            OpenExcelWriteData2("C:\Dotnet2010\MSDN\Excel\Basics_1\bin\Debug\KSG1.xlsx", "Sheet1", D)
            MessageBox.Show("Do it")
        Next

    End Sub

Add the following code to OpenWorkSheets.vb

Public Sub OpenExcelWriteData2(ByVal FileName As String, ByVal SheetName As String, ByVal DictCellData As Dictionary(Of String, String))
    Dim Proceed As Boolean = False

    Dim xlApp As Excel.Application = Nothing
    Dim xlWorkBooks As Excel.Workbooks = Nothing
    Dim xlWorkBook As Excel.Workbook = Nothing
    Dim xlWorkSheet As Excel.Worksheet = Nothing
    Dim xlWorkSheets As Excel.Sheets = Nothing
    Dim xlRange1 As Excel.Range = Nothing
    Dim xlInterior As Excel.Interior = Nothing
    Dim xlColumns As Excel.Range = Nothing

    xlApp = New Excel.Application
    xlApp.DisplayAlerts = False
    xlWorkBooks = xlApp.Workbooks
    xlWorkBook = xlWorkBooks.Open(FileName)

    xlApp.Visible = False

    xlWorkSheets = xlWorkBook.Sheets


    For x As Integer = 1 To xlWorkSheets.Count
        xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

        If xlWorkSheet.Name = SheetName Then
            Proceed = True
            Exit For
        End If

        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
        xlWorkSheet = Nothing

    Next

    If Proceed Then


        ' Write cell, dispose object, repeat...
        For Each Item In DictCellData
            xlRange1 = xlWorkSheet.Range(Item.Key)
            xlRange1.Value = Item.Value
            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing
        Next

        xlRange1 = xlWorkSheet.Range("A6")
        xlRange1.Value = "Total"

        Try
            '
            ' Delete comment if exists
            '
            If xlRange1.Comment IsNot Nothing Then
                xlRange1.Comment.Delete()
            End If

            xlRange1.AddComment("Total")
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        Marshal.FinalReleaseComObject(xlRange1)
        xlRange1 = Nothing

        xlRange1 = xlWorkSheet.Range("A7")
        xlRange1.Value = "Average Expense"
        Marshal.FinalReleaseComObject(xlRange1)
        xlRange1 = Nothing

        xlRange1 = xlWorkSheet.Range("B6")
        xlRange1.Formula = "=Sum(B2:B5)"
        Marshal.FinalReleaseComObject(xlRange1)
        xlRange1 = Nothing

        xlRange1 = xlWorkSheet.Range("B7")
        xlRange1.Formula = "=Average(B2:B5)"
        Marshal.FinalReleaseComObject(xlRange1)
        xlRange1 = Nothing

        xlRange1 = xlWorkSheet.Range("A1:B1,A6:A7")
        xlInterior = xlRange1.Interior
        xlInterior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)

        Dim TheFont = xlRange1.Font

        TheFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
        TheFont.Name = "Tahoma"
        TheFont.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
        TheFont.Bold = True

        Marshal.FinalReleaseComObject(TheFont)
        TheFont = Nothing

        xlRange1 = xlWorkSheet.Range("B2:B7")
        xlRange1.NumberFormat = "$#,##0.00"

        ' ******************************************************************
        ' An example of moving past tunneling and only calling the GC
        ' for one object. This can be avoided but wanted to show one
        ' example of calling the GC surrounded by other objects that
        ' need not call the GC.
        ' ******************************************************************
        xlColumns = CType(xlRange1.Columns("A:B"), Excel.Range)
        xlColumns.EntireColumn.AutoFit()
        releaseObject(xlColumns, True)

        Marshal.FinalReleaseComObject(xlRange1)
        xlRange1 = Nothing

        xlWorkSheet.SaveAs(FileName)
    Else
        ' IMPORTANT NOTE
        ' For production throw an exception, for demoing a message
        ' This demo the only way the sheet does not exists if someone
        ' really tried to mess with this code outside with MS-Excel.
        '
        MessageBox.Show(SheetName & " not located.")
    End If

    xlWorkBook.Close()
    xlApp.UserControl = True
    xlApp.Quit()

    ReleaseComObject(xlInterior)
    ReleaseComObject(xlRange1)
    ReleaseComObject(xlWorkSheets)
    ReleaseComObject(xlWorkSheet)
    ReleaseComObject(xlWorkBook)
    ReleaseComObject(xlWorkBooks)
    ReleaseComObject(xlApp)
End Sub

[robbie]

My biggest issue has been when I am opening the workbook the 2nd time.
If I open it up in Explorer (manually), it does NOT open in read-only mode but when I open it pro grammatically, it does.
Perhaps the key is using the Marshal object and using the ReleaseComObject.
I want to make sure that I do not have multiple instances of the Excel Object open in task manager.

[robbie]

Kevin,

Also, all I want to do is...
1. Create the Excel Application Object
2. Open the workbook.
3. Refresh the Pivot Tables in the workbook (2 pivot tables - 1 on two different worksheets)
4. Save the workbook.
5. Destroy the Excel Application Object

Loop back to number 1
My exception gets thrown on step 4 the 2nd time through the loop because the workbook keeps opening up in read-only mode.
Based on your responses, you believe that I am not properly shutting down the workbook and/or the excel application object?

[dunfiddlin]

OK. I'm coming in at the back of this and I haven't read all the posts in detail but I'm kinda wondering why you're doing this as a loop and attaching to single emails rather than just doing it once and attaching it to multiple emails or a single cc/bcc which would make the current problem disappear.

[robbie]

The data in the pivot tables changes with every iteration in the loop.
It's the same workbook but after the refresh, the data is different.
If the data was the same, I would only send it once to everybody who needs the workbook but that's not what's happening here...

[dunfiddlin]

Ok. Just checking. I'll take another look at this tomorrow in more detail. I'd do it now but my dogs look like they'll explode if they don't get out for a pee soon!

[kevininstructor]

Kevin,

Also, all I want to do is...
1. Create the Excel Application Object
2. Open the workbook.
3. Refresh the Pivot Tables in the workbook (2 pivot tables - 1 on two different worksheets)
4. Save the workbook.
5. Destroy the Excel Application Object

Loop back to number 1
My exception gets thrown on step 4 the 2nd time through the loop because the workbook keeps opening up in read-only mode.
Based on your responses, you believe that I am not properly shutting down the workbook and/or the excel application object?

Using my code there is only one way for you to get "read-only" is if Excel not code has the file open which I just tested. Try the revised version below, change is If xlWorkBook.ReadOnly Then

    Public Sub OpenExcelWriteData2(ByVal FileName As String, ByVal SheetName As String, ByVal DictCellData As Dictionary(Of String, String))
        Dim Proceed As Boolean = False

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlRange1 As Excel.Range = Nothing
        Dim xlInterior As Excel.Interior = Nothing
        Dim xlColumns As Excel.Range = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then
                Proceed = True
                Exit For
            End If

            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing

        Next

        If Proceed Then
            '
            ' Determine if someone outside of this app has the file open 
            ' by opening it via Excel not code.
            '
            If xlWorkBook.ReadOnly Then
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()

                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)

                ' get from My_Dialogs.vb
                My.Dialogs.ExceptionDialog("Cannot continue, file is open read-only by someone else")

                Exit Sub

            End If


            ' Write cell, dispose object, repeat...
            For Each Item In DictCellData
                xlRange1 = xlWorkSheet.Range(Item.Key)
                xlRange1.Value = Item.Value
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
            Next

            xlRange1 = xlWorkSheet.Range("A6")
            xlRange1.Value = "Total"

            Try
                '
                ' Delete comment if exists
                '
                If xlRange1.Comment IsNot Nothing Then
                    xlRange1.Comment.Delete()
                End If

                xlRange1.AddComment("Total")
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing

            xlRange1 = xlWorkSheet.Range("A7")
            xlRange1.Value = "Average Expense"
            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing

            xlRange1 = xlWorkSheet.Range("B6")
            xlRange1.Formula = "=Sum(B2:B5)"
            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing

            xlRange1 = xlWorkSheet.Range("B7")
            xlRange1.Formula = "=Average(B2:B5)"
            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing

            xlRange1 = xlWorkSheet.Range("A1:B1,A6:A7")
            xlInterior = xlRange1.Interior
            xlInterior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)

            Dim TheFont = xlRange1.Font

            TheFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
            TheFont.Name = "Tahoma"
            TheFont.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
            TheFont.Bold = True

            Marshal.FinalReleaseComObject(TheFont)
            TheFont = Nothing

            xlRange1 = xlWorkSheet.Range("B2:B7")
            xlRange1.NumberFormat = "$#,##0.00"

            ' ******************************************************************
            ' An example of moving past tunneling and only calling the GC
            ' for one object. This can be avoided but wanted to show one
            ' example of calling the GC surrounded by other objects that
            ' need not call the GC.
            ' ******************************************************************
            xlColumns = CType(xlRange1.Columns("A:B"), Excel.Range)
            xlColumns.EntireColumn.AutoFit()
            releaseObject(xlColumns, True)

            Marshal.FinalReleaseComObject(xlRange1)
            xlRange1 = Nothing
            xlWorkSheet.SaveAs(FileName)


        Else
            ' IMPORTANT NOTE
            ' For production throw an exception, for demoing a message
            ' This demo the only way the sheet does not exists if someone
            ' really tried to mess with this code outside with MS-Excel.
            '
            MessageBox.Show(SheetName & " not located.")
        End If

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlInterior)
        ReleaseComObject(xlRange1)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    End Sub

[Hedgepiglet]

Hello Kevin
Is there a problem in the updated code?
The line:
My.Dialogs.ExceptionDialog("Cannot continue, file is open read-only by someone else")
generates the error "Dialogs" is not a member of My.
(Sorry, I don't know enough Visual Basic to know if I'm doing something stupid or if there is a problem!)

[Hedgepiglet]

Another question for Kevin

When searching for the worksheet you want, couldn't you do something like:
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetName), Excel.Worksheet)
instead of looping through all the exisiting sheets and checking on the name property?
I guess with the above code you might need to trap the error if SheetName doesn't exist at all.

Am I missing a good reason for following the approach you took?
(Apologies for hijacking robbie's thread!)

[kevininstructor]

Hello Kevin
Is there a problem in the updated code?
The line:
My.Dialogs.ExceptionDialog("Cannot continue, file is open read-only by someone else")
generates the error "Dialogs" is not a member of My.
(Sorry, I don't know enough Visual Basic to know if I'm doing something stupid or if there is a problem!)

The code for My.Dialogs.ExceptionDialog is in the MSDN project in file My_Dialogs.vb

[kevininstructor]

Another question for Kevin

When searching for the worksheet you want, couldn't you do something like:
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetName), Excel.Worksheet)
instead of looping through all the exisiting sheets and checking on the name property?
I guess with the above code you might need to trap the error if SheetName doesn't exist at all.

Am I missing a good reason for following the approach you took?
(Apologies for hijacking robbie's thread!)

No need to apologize, most developers fall prey to taking an easy approach to creating and disposing of objects used to access Excel. I believe the reason is there is not much thought in how these objects are disposed of. If you have time check out this MVP site where I assisted in the article for disposing objects. Also I would urge anyone doing Excel automation to visit Sid's site, he is an expert with Excel automation.

Using the following will require you make several calls to the GC while the looping does not.

xlWorkSheet = CType(xlWorkBook.Worksheets(SheetName), Excel.Worksheet)

The same for the following using an ordinal which has another issue in that if we want sheet1 and sheet1 is ordinal position 5 because the user created addition sheets and moved sheet1.

xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

[robbie]

Kevin,

Thanks again for all your time with this dilemma I'm having.
A couple of other points I forgot to mention.

In your If condition, your comment above talks about checking if someone outside of this app has the workbook open.
This is my confusion with closing and destroying the Excel application object.
I'm positive nobody has the workbook open so it's frustrating when I re-open the workbook only to see that is has opened in read-only mode.
I am changing my code to early-binding and will post my results once I'm finished.

Consider my workbook much like a template.
1. I execute a stored procedure which puts my result set in a sql table prior to launching the Excel object and opening up the workbook.
2. I run the refresh query method which populates my query table with fresh results.
3. I save and close the workbook, then, destroy the Excel object.
4. I e-mail the workbook out to chosen recipients.
5. I start over again at step 1 until all of my reports have been sent out via e-mail using the SAME WORKBOOK.

This is an application that runs UN-attended and is scheduled on a xp task scheduler that runs at the beginning of every month.

[kevininstructor]

Reading back your last reply, if you followed my code methods to the letter you would not have an issue.

A good example of causing disposal issue is having two dots in a line of code for Excel. Immediately the With is one dot for instance then the last line is bad as it has several dots.

With oApp

    ' ----------------------------------------------------- '
    ' turn these properties to false to speed up processing '
    ' ----------------------------------------------------- '
    .Visible = True
    .DisplayAlerts = False
    '.Interactive = False
    '.ScreenUpdating = False

    ' ----------------------- '
    ' open the excel workbook '
    ' ----------------------- '
    .Workbooks.Open(Excel_File_Path, , False)

[Hedgepiglet]

Kevin - thanks for your answers to my questions as well as the link to Sid's site, which looks very interesting. I have a lot to learn about disposal of com objects as I'd naively imagined VB to handle all of this.

I must confess that your answer about why to loop rather than refer by the sheetname came as quite a surprise to me, as one coming from a VBA background. I was even more surprised when I tested it to find that looping was a lot quicker!

[Hedgepiglet]

robbie, this probably isn't much help but while fiddling around, I came across a very similar problem.
It turned out that Excel was still sitting in memory and the spreadsheet was open. However, since I had ExcelApp.Visible = FALSE, I could't see it. I only noticed when I checked what processes were running. The problem had occurred because I had interrupted the code while debugging, before the instance of Excel had been closed.

[robbie]

Kevin,

I'm having problems formatting the refresh method. Here is what I have (I could not find an enum for the backgroundrefresh property)

This is what I have but it doesn't work.
xlRange = CType(CType(xlApp.Range("Rpt_Starting_Cell_Hdr"), Excel.Range).Select, Excel.Range).ListObject.QueryTable.Refresh(CType(False, Boolean))

[kevininstructor]

Kevin - thanks for your answers to my questions as well as the link to Sid's site, which looks very interesting. I have a lot to learn about disposal of com objects as I'd naively imagined VB to handle all of this.

Don’t feel too bad about disposal of Excel objects as the majority of .NET developers are in the same boat. I believe that somewhere in time past that people migrated VBA code to VB6 and .NET and a) did not consider disposal in these languages or b) did realize this and excepted the fact these objects would only release under normal termination of the app. Couple that with said code would be used seldom so not much to worry about which is fine if you understand thing and wrong if you blindly use the code without any thought of cleaning up objects.

I must confess that your answer about why to loop rather than refer by the sheetname came as quite a surprise to me, as one coming from a VBA background. I was even more surprised when I tested it to find that looping was a lot quicker!

Woohoo

[kevininstructor]

Kevin,

I'm having problems formatting the refresh method. Here is what I have (I could not find an enum for the backgroundrefresh property)

This is what I have but it doesn't work.
xlRange = CType(CType(xlApp.Range("Rpt_Starting_Cell_Hdr"), Excel.Range).Select, Excel.Range).ListObject.QueryTable.Refresh(CType(False, Boolean))

I have not worked with QueryTable before but did a quick search on Google and found two links which assisted me with the following code. Important, this code is not tested and is presented in a effort to show how you would access QueryTable by WorkSheet or Range. Make sure to read thru the comments. Hope this helps.

Code was written in the same MSDN project I mentioned earlier that I did

Option Strict On
Option Infer On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module Module1
    Public Sub Excel_Test1a(ByVal FileName As String, ByVal SheetName As String, ByVal CellAddress As String)

        If IO.File.Exists(FileName) Then

            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlRange As Excel.Range = Nothing
            Dim xlListObject As Excel.ListObject = Nothing
            Dim xlQueryTable As Excel.QueryTable = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing


            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then
                '
                ' The conditional if below is one way to show two different 
                ' approaches to getting to the QueryTable, by range or worksheet.
                '
                '
                ' For you cut out the entire IF statement, then decide on one to 
                ' try and paste only that one back in. If one does not work try
                ' the other.
                '
#If NOT_FROM_RANGE Then
                xlListObject = xlWorkSheet.ListObjects(1)
                xlQueryTable = xlListObject.QueryTable
                xlQueryTable.Refresh() '
#Else
                xlRange = xlWorkSheet.Range("")
                xlListObject = xlRange.ListObject()
                xlQueryTable = xlListObject.QueryTable
                xlQueryTable.Refresh() '
#End If
            Else
                MessageBox.Show(SheetName & " not found.")
            End If


            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlListObject)
            ReleaseComObject(xlQueryTable)
            ReleaseComObject(xlRange)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
        End If
    End Sub
End Module