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?
User contributions licensed under CC BY-SA 3.0