I have a workbook with some macros in it, they are:
-ThisWorkbook which does things when the file opens
-Module1 - Holds some code
-frmGui - A gui form (designer) with code behind
I’m trying to get Aspose.Cells to copy the VBA (and the form) from one workbook to another. The key bit of my code is
'add any macros from the template workbook
For Each vbaItem As Vba.VbaModule In tp.VbaProject.Modules
If vbaItem.Name = "ThisWorkbook" Then
wb.VbaProject.Modules("ThisWorkbook").Codes = vbaItem.Codes
ElseIf vbaItem.Codes Like "*'[AUTO COPY TO TARGET]*" Then
Debug.Print(vbaItem.Name)
Dim vbaMod As Integer
vbaMod = wb.VbaProject.Modules.Add(vbaItem.Type, vbaItem.Name)
wb.VbaProject.Modules(vbaMod).Codes = vbaItem.Codes
End If
Next
where tp is the workbook with the macros, and wb is the workbook where i want to copy them.
All the relevant segments of code are triggering however if I copy the code for the form (designer), Once i save the file and try to open it Excel claims the workbook is corrupt and needs repairing. If you say Yes it comes back with “Removed Part: /xl/vbaProject.bin part.”
Please provide us your Microsoft Visual Studio Console Application Project which should be Runnable along with source Excel file(s) and which should replicate your error.
We will execute your project at our end and replicate the problem and log it in our database for product team investigation and for a fix. Thanks for your cooperation in this regard and have a good day.
Please change your code about copying vba modules as given below.
VB.NET
'Copy the vba code from template > target
For Each vbaItem As Vba.VbaModule In templateFile.VbaProject.Modules
If vbaItem.Name = "ThisWorkbook" Then
target.VbaProject.Modules("ThisWorkbook").Codes = vbaItem.Codes
Else
Debug.Print(vbaItem.Name)
Dim vbaMod As Integer
Dim sheet As Worksheet = target.Worksheets.GetSheetByCodeName(vbaItem.Name)
If sheet Is Nothing Then
vbaMod = target.VbaProject.Modules.Add(vbaItem.Type, vbaItem.Name)
Else
vbaMod = target.VbaProject.Modules.Add(sheet)
End If
target.VbaProject.Modules(vbaMod).Codes = vbaItem.Codes
End If
Next
We do not support parsing designer storage in VBA project, so the copied VBA project gets corrupted.
We will look into how to provide those data, but it’s complex. We hope, we can support it in the next month.
Just for the avoidance of doubt, I am a paying Aspose customer (and a general evangelist of how useful it is), however my company purchases all the licences through procurement and makes it non-trivial to get all the relevant details to get me into the paid support forum.
Your both of issues (i.e. CELLSNET-45588 and CELLSNET-45589) are marked as Resolved, so we are hopeful, you will get a fix soon. Once, we will have a fix for you, we will update you in this thread.
Please try the following sample code with latest version/fix: Aspose.Cells for .NET v17.8.4:
e.g Sample code:
Imports Aspose.Cells
Imports Aspose.Cells.Vba
Public Class Form1
Dim RootPath As String = "C:\Users\Administrator\Downloads\Demo_TemplateImportBroken\Demo_TemplateImportBroken\"
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim target As New Workbook(RootPath & "TargetFile.xlsx")
Dim templateFile As New Workbook(RootPath & "TemplateFileToImport.xlsm")
For Each ws As Worksheet In templateFile.Worksheets
'If it is a sheet, copy it over from template > target
If ws.Type = SheetType.Worksheet Then
Dim s As Worksheet
s = target.Worksheets.Add(ws.Name)
s.Copy(ws)
End If
Next
'^^^^^^Above here works OK^^^^^^^^
'Copy the vba code from template > target
For Each vbaItem As Vba.VbaModule In templateFile.VbaProject.Modules
If vbaItem.Name = "ThisWorkbook" Then
target.VbaProject.Modules("ThisWorkbook").Codes = vbaItem.Codes
Else
Debug.Print(vbaItem.Name)
Dim vbaMod As Integer
Dim sheet As Worksheet = target.Worksheets.GetSheetByCodeName(vbaItem.Name)
If sheet Is Nothing Then
vbaMod = target.VbaProject.Modules.Add(vbaItem.Type, vbaItem.Name)
Else
vbaMod = target.VbaProject.Modules.Add(sheet)
End If
target.VbaProject.Modules(vbaMod).Codes = vbaItem.Codes
If (vbaItem.Type = VbaModuleType.Designer) Then
target.VbaProject.Modules.AddDesignerStorage(vbaItem.Name, templateFile.VbaProject.Modules.GetDesignerStorage(vbaItem.Name))
End If
End If
Next
'Save the workbook
'*********Throws an error********
target.Save(RootPath & "out.xlsm", SaveFormat.Xlsm)
'Tidy up
templateFile = Nothing
target = Nothing
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim target As New Workbook(RootPath & "TargetFile.xlsx")
'Issue number 2 - I HAVE CHANGED THE TEMPLATE FILE TO GET AROUND ISSUE 1, BUT A NEW ISSUE HAPPENS
Dim templateFile As New Workbook(RootPath & "TemplateFileToImport v2.xlsm")
For Each ws As Worksheet In templateFile.Worksheets
'If it is a sheet, copy it over from template > target
If ws.Type = SheetType.Worksheet Then
Dim s As Worksheet
s = target.Worksheets.Add(ws.Name)
s.Copy(ws)
End If
Next
'^^^^^^Above here works OK^^^^^^^^
'Copy the vba code from template > target
For Each vbaItem As Vba.VbaModule In templateFile.VbaProject.Modules
If vbaItem.Name = "ThisWorkbook" Then
target.VbaProject.Modules("ThisWorkbook").Codes = vbaItem.Codes
ElseIf vbaItem.Type = Vba.VbaModuleType.Designer Then '<<V2 JUST TRYING THE DESIGNER (SKIPPING THE SHEET CLASSES)
Debug.Print(vbaItem.Name)
Dim vbaMod As Integer
vbaMod = target.VbaProject.Modules.Add(vbaItem.Type, vbaItem.Name)
target.VbaProject.Modules(vbaMod).Codes = vbaItem.Codes
target.VbaProject.Modules.AddDesignerStorage(vbaItem.Name, templateFile.VbaProject.Modules.GetDesignerStorage(vbaItem.Name))
End If
Next
'Save the workbook
target.Save(RootPath & "out V2.xlsm", SaveFormat.Xlsm)
'*** THE SAVED FILE CLAIMS THE VBA IS CORRUPT. IT IS THEN DELETED SO YOU CANNOT VIEW IT.
'Tidy up
templateFile = Nothing
target = Nothing
End Sub
End Class
Good to know that your issue is sorted out by the suggested code. Feel free to write us back if you need further help or have some other issue or queries, we will be happy to assist you soon.
