Issue with Worksheet.Copy method from resource in VSTO

1

I am experiencing a problem with Excel Worksheet.Copy method in VSTO. The worksheet to be copied is stored as a resource file. The resource file is read every time the template needs to be copied. The code works perfectly fine when the template sheet is imported for the first time. However, when I delete the the template sheet immediately and attempt to insert the same resulting in the error.

I tried to catch the exception. But the code does not catch the exception and result in the error and excel crashes. When the excel application is restarted, it copy the sheet perfectly. At any one session, I can import the template sheet only once.

   Dim tempName As String
   Dim oTemplate As Excel.Workbook
   Try
     ResMgr = New Resources.ResourceManager("MYUtilities.Resources", 
     System.Reflection.Assembly.GetExecutingAssembly)
     tempName = "template.xlsx"
     tempName = Globals.ThisAddIn.Application.ActiveWorkbook.Path & "\" & 
     tempName
     Dim fstream As New FileStream(tempName, FileMode.Create, 
     FileAccess.Write)
     Dim filestreamWrite As New BinaryWriter(fstream)
     filestreamWrite.Write(My.Resources.template, 0, 
     My.Resources.template.Length)
     fstream.Close()
     filestreamWrite.Close()
     oTemplate = xlApp.Workbooks.Add(tempName)

     If Not WorksheetExists("TemplateSheet") Then
        oTemplate.Worksheets.Copy(, wb.Worksheets("StartSheet"))
     End If
     oTemplate.Close()  
     My.Computer.FileSystem.DeleteFile(tempName) 
     ResMgr.ReleaseAllResources() 

     MessageBox.Show("Template Sheet Added...")
 Catch ex As Exception
    oTemplate.Close() 'line inserted during the trial
    My.Computer.FileSystem.DeleteFile(tempName) 'trial line
    ResMgr.ReleaseAllResources() 'line inserted during the trial
    MessageBox.Show("Template Sheet not added.")
 End Try

The WorksheetExists function looks as below

   Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
        WorksheetExists = False
        Dim Sht As Worksheet
        For Each Sht In wb.Worksheets
            If xlApp.Application.Proper(Sht.Name) = 
             xlApp.Application.Proper(WorksheetName) Then
                WorksheetExists = True
                Exit Function
            End If
        Next Sht

    End Function

wb and xlApp are defined as a global variables

   Dim xlApp As Excel.Application = Globals.ThisAddIn.Application
   Dim wb As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook

When I run the code it works perfectly well in the first click. If the template sheet is deleted and if I try to insert the template again, it gives me the following error at the line.

oTemplate.Worksheets.Copy(, wb.Worksheets("StartSheet"))

System.AccessViolationException HResult=0x80004003 Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Source= StackTrace:

If I restart the excel application then the code works perfectly alright. What it is not allowing is inserting the template sheet and deleting it and inserting the same template sheet again in the same session. Am I missing something?

excel
vb.net
vsto
asked on Stack Overflow Jun 22, 2019 by Raghu • edited Jun 23, 2019 by aduguid

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0