[RESOLVED] Problems Manipulating Excel from Console App

[robbie]

I'm setting up a console application that ...

1. Executes a stored procedure that populates a SQL table for reporting purposes.

2. Opens an Excel Workbook.

3. Refreshes the Workbook.

4. Saves the Workbook.

5. Closes the Workbook.

6. Sends the Workbook as an attachmate in an e-mail using the SmtpClient class.

Now, steps 1 through 6 are inside of a loop as I would like to use the same workbook for all my report refreshes.

My perfect solution is to iterate this loop as many time as needed to have all my reports automatically sent via email as an attachmate.

The first time through the loop, it works perfectly, however, the 2nd time through the loop, I noticed when the application opens up the workbook, the workbook opens up as a read-only copy even though I properly closed the workbook prior to re-opening it.

I've tried to force the open as read/write but then, the 2nd time the workbook loads, an exception is thrown.

I'm on Windows 7 and I noticed that the folder properties has a read-only check box which I make sure is cleared before running the application but then when I stop/pause the application to inspect the folder properties, the check box is checked again. I'm not sure if this is why I'm having issues when I open up the Workbook a 2nd time or not.

If anybody knows how I can get past this. Please reply. It is greatly appreciated.

[kareninstructor]

Hello,

There are multiple methods of working with Excel i.e. early and late binding, OpenXML SDK, OleDb etc.

Without seeing your code that exposes the operations you are questioning we cannot assist.

[robbie]

I am using late binding. Here is my procedure.

    Public Function RefreshEngine() As Boolean

        Try

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

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

                Dim oApp As Object = CreateObject("Excel.Application")

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

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

                    '''''''''''''''''''''''''''
                    ' open the excel workbook '
                    '''''''''''''''''''''''''''
                    Dim blnProceed As Boolean = False
                    Dim loopCounter As Integer = 0

                    Do

                        .Workbooks.Open(Excel_File_Path)

                        If .ActiveWorkbook.ReadOnly = True Then

                            loopCounter += 1
                            Dim DelayTime = New TimeSpan(0, 0, loopCounter)
                            Thread.Sleep(DelayTime)

                            .ActiveWorkbook.ChangeFileAccess(xlReadWrite)

                            blnProceed = False
                            If .ActiveWorkbook.ReadOnly = True Then
                                .ActiveWorkbook.Close()
                                blnProceed = False
                            Else
                                blnProceed = True
                            End If

                        Else
                            blnProceed = True
                        End If

                    Loop Until blnProceed = True

                    ''''''''''''''''''''''''''''
                    ' refresh the 1st Worksheet '
                    ''''''''''''''''''''''''''''
                    .Sheets("sheet1").Visible = True
                    .Sheets("sheet1").Select()
                    .Range("A1").Select()
                    .Selection.ListObject.QueryTable.Refresh(False)
                    .Sheets("sheet1").Visible = False

                    ''''''''''''''''''''''''''''
                    ' refresh the 2nd Worksheet '
                    ''''''''''''''''''''''''''''
                    .Sheets("sheet2").Select()
                    .Range("A13").Select()
                    .Selection.ListObject.QueryTable.Refresh(False)

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

                End With

                ''''''''''''''
                ' setup mail '
                ''''''''''''''
                R(X).Email_Success = SendMail(X)

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

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

                '''''''''''''''''''''''''''''''''
                ' quit and destroy excel object '
                '''''''''''''''''''''''''''''''''
                oApp.Quit()
                oApp = Nothing

            Next X

        Catch ex As Exception

            ''''''''''''''''''''''''''''''''''''''''
            ' update log string and console screen '
            ''''''''''''''''''''''''''''''''''''''''
            strLog &= Format(Now(), tstamp) & "Exception Encountered in RefreshEngine:" & vbCrLf & ex.ToString & vbCrLf
            Console.WriteLine(Format(Now(), tstamp) & "Exception Encountered in RefreshEngine: " & ex.ToString)

            ''''''''''''''''''''''''
            ' update execution log '
            ''''''''''''''''''''''''
            CreateLogFile()

            '''''''''''''''''''''''''''''''''''''''''''''''
            ' exit application with a custom error number '
            '''''''''''''''''''''''''''''''''''''''''''''''
            Environment.Exit(intErrRefreshEngine)

        End Try

    End Function

The refresh piece works, its the 2nd iteration where the problem exists. Please let me know if there is anything other code you need as well. Thanks.

[kareninstructor]

How is Excel_File_Path being set inside the For/Next? I assume the first time thru you have already set it but inside I see nothing changing this variable.

Also which line is raising an exception? You can step thru the code with the IDE debugger or simply (for now) remove the Try/Catch and see which line is causing the exception.

Also what is the message exactly given when the exception is thrown?

[robbie]

>> How is Excel_File_Path being set inside the For/Next? I assume the first time thru you have already set it but inside I see nothing changing this variable. <<

I'm using the same workbook for all my refreshes. I go out to SQL to change the result set of the data. The file name variable is simply the unc path and filename to use in this operation.

>> Also which line is raising an exception? You can step thru the code with the IDE debugger or simply (for now) remove the Try/Catch and see which line is causing the exception. <<

I used to open the Workbook as .Workbooks.Open(Excel_File_Path,0,False) to try and force the workbook open as read/write instead of read only. This would be the line that throws the exception.

>> Also what is the message exactly given when the exception is thrown? <<

When I would try to change the file access to read/write prior to calling the refreshes or the save, this would be the exception I get.

System.Runtime.InteropServices.COMException (0x800A03EC): Unable to get the ChangeFileAccess property of the Workbook class
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

[kareninstructor]

>> How is Excel_File_Path being set inside the For/Next? I assume the first time thru you have already set it but inside I see nothing changing this variable. <<

I'm using the same workbook for all my refreshes. I go out to SQL to change the result set of the data. The file name variable is simply the unc path and filename to use in this operation.

>> Also which line is raising an exception? You can step thru the code with the IDE debugger or simply (for now) remove the Try/Catch and see which line is causing the exception. <<

I used to open the Workbook as .Workbooks.Open(Excel_File_Path,0,False) to try and force the workbook open as read/write instead of read only. This would be the line that throws the exception.

>> Also what is the message exactly given when the exception is thrown? <<

When I would try to change the file access to read/write prior to calling the refreshes or the save, this would be the exception I get.

System.Runtime.InteropServices.COMException (0x800A03EC): Unable to get the ChangeFileAccess property of the Workbook class
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

It might be worth altering how you are doing this by opening the file once, looping thru then when done then close the file.

[robbie]

Actually, that is how I had it originally. Same issue. The 2nd time the workbook is opened, Excel automatically opens it up in read-only mode so when the automation goes to save, the save never takes place. This will be running in unattended mode which is why I've turned off the display alerts...

[kareninstructor]

I have never seen this exception before. Also search Google and it appears there is nothing out there on it.

Try the following, see red color code for additions. Usually calling the Garbage Collector is not something done unless absolutely nessecary but worth trying here.

Public Function RefreshEngine() As Boolean

    Try

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

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

            Dim oApp As Object = CreateObject("Excel.Application")

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

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

                '''''''''''''''''''''''''''
                ' open the excel workbook '
                '''''''''''''''''''''''''''
                Dim blnProceed As Boolean = False
                Dim loopCounter As Integer = 0

                Do

                    .Workbooks.Open(Excel_File_Path)

                    If .ActiveWorkbook.ReadOnly = True Then

                        loopCounter += 1
                        Dim DelayTime = New TimeSpan(0, 0, loopCounter)
                        Thread.Sleep(DelayTime)

                        .ActiveWorkbook.ChangeFileAccess(xlReadWrite)

                        blnProceed = False
                        If .ActiveWorkbook.ReadOnly = True Then
                            .ActiveWorkbook.Close()
                            blnProceed = False
                        Else
                            blnProceed = True
                        End If

                    Else
                        blnProceed = True
                    End If

                Loop Until blnProceed = True

                ''''''''''''''''''''''''''''
                ' refresh the 1st Worksheet '
                ''''''''''''''''''''''''''''
                .Sheets("sheet1").Visible = True
                .Sheets("sheet1").Select()
                .Range("A1").Select()
                .Selection.ListObject.QueryTable.Refresh(False)
                .Sheets("sheet1").Visible = False

                ''''''''''''''''''''''''''''
                ' refresh the 2nd Worksheet '
                ''''''''''''''''''''''''''''
                .Sheets("sheet2").Select()
                .Range("A13").Select()
                .Selection.ListObject.QueryTable.Refresh(False)

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

            End With

            ''''''''''''''
            ' setup mail '
            ''''''''''''''
            R(X).Email_Success = SendMail(X)

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

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

            '''''''''''''''''''''''''''''''''
            ' quit and destroy excel object '
            '''''''''''''''''''''''''''''''''
            oApp.Quit()
            oApp = Nothing
            
            GC.Collect()
            GC.WaitForPendingFinalizers()
            ' GC 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 the finalizing. Only 
            ' then will the object do its automatic ReleaseComObject.
            GC.Collect()
            GC.WaitForPendingFinalizers()

        Next X

    Catch ex As Exception

        ''''''''''''''''''''''''''''''''''''''''
        ' update log string and console screen '
        ''''''''''''''''''''''''''''''''''''''''
        strLog &= Format(Now(), tstamp) & "Exception Encountered in RefreshEngine:" & vbCrLf & ex.ToString & vbCrLf
        Console.WriteLine(Format(Now(), tstamp) & "Exception Encountered in RefreshEngine: " & ex.ToString)

        ''''''''''''''''''''''''
        ' update execution log '
        ''''''''''''''''''''''''
        CreateLogFile()

        '''''''''''''''''''''''''''''''''''''''''''''''
        ' exit application with a custom error number '
        '''''''''''''''''''''''''''''''''''''''''''''''
        Environment.Exit(intErrRefreshEngine)

    End Try

End Function

[robbie]

Thanks Kevin. The garbage collection methods worked. I tried to keep the Excel application alive and put the looping back the way I had it but it didn't work so I have to create and destroy the Excel application object, then run garbage collection to get it to work. Thanks for all your help...