We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copy VBA project from one workbook to another

I’m using Aspose.Cells for .NET 17.3.5.0

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.”

Any thoughts on how I can resolve this issue.

Many thanks

@AntEY

Thanks for your posting and using Aspose APIs.

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.

Hi,

Please find attached a small standalone project which recreates the two issues I’m having. Any questions please let me know.

You’ll need to update the RootPath variable but it is pretty obvious.

https://drive.google.com/open?id=0B4D5BGxcN6S4cExxZThfUlhWRUE

Thanks
Anthony

@AntEY

Thanks for using Aspose APIs.

We were able to observe both of your issues and logged them in our database for investigation and for a fix.

Once, the issues are resolved or we have some other news for you, we will let you know asap.

These issues have been logged as

  • CELLSNET-45588 - Excel has to repair the Macro file - Removed Part: /xl/vbaProject.bin part
  • CELLSNET-45589 - Item has already been added. Key in dictionary: ‘Sheet1’ Key being added: ‘Sheet1’

@AntEY

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.

Hi,

Thanks for the amended code, I’ve given this a try and it appears to be working for non-designer modules.

I will keep an eye on this thread for the implementation of Designer copy capability as this will be hugely helpful to my project.

Thanks for taking the time to investigate so promptly, and scheduling the development. I really do appreciate it.

Regards
Anthony

@AntEY

Thanks for your feedback and considering Aspose APIs.

It is good to know that your issue is resolved partially. We will keep you updated with any progress in these tickets. Thank you and have a good day.,

Hi,

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.

Thanks for looking into it.
Anthony

@AntEY

Thanks for using Aspose APIs.

FYI:

There are two types of paid users.

1 - One who have bought the license of Aspose API (component) e.g.

  • License of Aspose.Total
  • License of Aspose.Cells
  • License of Aspose.Words

2 - One who have bought the license of Aspose Support itself.

Are you category 1 customer or category 2 customer or both?

If you are category 2 customer, then you should click on Paid Support instead of Free Support.

Please see this screenshot.

Screenshot
sc.png (89.7 KB)

Category 1 I think :frowning:

@AntEY

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.

Great news - I wait with bated breath for the fixes to land in a version I can test!

@AntEY

Please download and try the following fix and let us know your feedback.

Aspose.Cells for .NET (Latest Version)

Hi,

Unfortunately I still get the issue w.r.t. CELLSNET-45588 when I try to import the Form Designer.

Any thoughts? Or do you have a now-working example I can use to try to get it working?

Thanks

@AntEY,

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

Let us know your feedback.

Ah Ha,

I wouldn’t have spotted the addition of:

  • VbaProject.Modules.AddDesignerStorage
  • VbaProject.Modules.GetDesignerStorage

Now that I have this information I have it working very well. Case closed, another excellent example of your fantastic customer support.

Thanks a lot
Anthony

@AntEY,

Thanks for your feedback.

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.

The issues you have found earlier (filed as CELLSNET-45588;CELLSNET-45589) have been fixed in Aspose.Cells for .NET (Latest Version)