CODEWITHSUNDEEP
excelvb.netvsto
Virtual Underscore
Virtual Underscore

Reputation: 13

Copying worksheets between different instance of Excel.Application

I am working on a VSTO Excel add-in where at some point, it will open a template workbook from Resources and copy a sheet from it to the running instance of Excel. I want to avoid the short white window flash when copying from the template so I created a hidden instance of Excel.Application and call it from there. This part works but when copying, I keep getting "System.Runtime.InteropServices.COMException: 'Copy method of Worksheet class failed'"

Dim tempFileName As String = "DesignWorks1_Template"
Dim tempName As String = Path.GetTempPath() & "\" & tempFileName & ".xlsx"
Dim ResMgr = New Resources.ResourceManager("MyUtilities.Resources", System.Reflection.Assembly.GetExecutingAssembly)
Dim fstream As New FileStream(tempName, FileMode.Create, FileAccess.Write)
Dim filestreamWrite As New BinaryWriter(fstream)
filestreamWrite.Write(My.Resources.DesignWorks1, 0, My.Resources.DesignWorks1.Length)
fstream.Close()
filestreamWrite.Close()

Dim currentWorkbook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
Dim newHiddenApp As New Excel.Application
newHiddenApp.Visible = False
Dim oTemplate As Excel.Workbook = newHiddenApp.Workbooks.Add(tempName)
oTemplate.Worksheets(compareName).Copy(currentWorkbook.Worksheets(1)) 'error here
oTemplate.Close()
My.Computer.FileSystem.DeleteFile(tempName)
ResMgr.ReleaseAllResources()

Thanks in advance.

Upvotes: 1

Views: 147

Answers (2)

TnTinMn
TnTinMn

Reputation: 11801

Is it possible to specify a particular sheet from the template? I tried Sheets.Add but it seems to import all worksheets from the specified path. – Virtual Underscore

This is the major pain point in exporting each sheet in a workbook to a WorkSheet template file. When you perform the save-as Worksheet template, the subject Worksheet must be the only Worksheet in the Workbook. If more than one Worksheet exists in the Workbook, you will be exporting a Workbook template.

You can copy the following VBA code to the ThisWorkbook code file of the Workbook you want to export templates from. Make sure you modify the templateFolder = "F:\Temp\Templates" line in the code. Run it and it will export each Worksheet in the Workbook.

Sub ExportWorksheetTemplates()
   Excel.Application.DisplayAlerts = False
   Excel.Application.ScreenUpdating = False

   Dim templateFolder As String
   templateFolder = "F:\Temp\Templates"   ' set this to an existing folder on you system

   Dim tmpWb As Excel.Workbook
   Dim old_numsheets As Integer

   old_numsheets = Excel.Application.SheetsInNewWorkbook
   Excel.Application.SheetsInNewWorkbook = 1

   Dim ws As Excel.Worksheet
   Dim newWBFirstSheet As Excel.Worksheet

   Dim templatePath As String

   On Error GoTo Finalize

   For Each ws In ThisWorkbook.Worksheets
      Set tmpWb = Excel.Application.Workbooks.Add()
      Set newWBFirstSheet = tmpWb.Worksheets(1)
      newWBFirstSheet.Name = "."
      ws.Copy after:=newWBFirstSheet
      newWBFirstSheet.Delete


      templatePath = templateFolder & "\" & tmpWb.Worksheets(1).Name & ".xltx"
      tmpWb.Worksheets(1).SaveAs templatePath, Excel.XlFileFormat.xlOpenXMLTemplate

      tmpWb.Close
   Next

Finalize:
   Excel.Application.DisplayAlerts = True
   Excel.Application.ScreenUpdating = True
   Excel.Application.SheetsInNewWorkbook = old_numsheets
End Sub

Now you can add each of those files to your VSTO project's resources.

Then you would export the resource templates to a temporary file as you are currently doing.

When you use the Sheets.Add method and specify Type:="path to template", you will now only get the single Worksheet added to the Workbook.

Upvotes: 0

teylyn
teylyn

Reputation: 35990

When running Excel on the desktop, you cannot copy sheets between instances. Therefore I doubt that it can be done when the Excel instances are initiated with VB.

In order to copy sheets between workbooks, these need to run in the same instance.

Upvotes: 0

Related Questions