Convert Excel Macro to C#
Budget: $250 – $750 USD
We have a VB.NET application that populates the attached spreadsheet using a combination of EPPlus and OLE automation. The OLE automation is used to execute a few macros in the sheet. One of the macros called by our VB.NET app is the wksPhotos.PhotoAppend2 macro. This macro is essentially a duplicate of the wksPhotos.Append macro that is invoked when the user manually clicks the Add Photos button on the Photos worksheet, but the PhotoAppend2 macro is designed to take a jpg file, photo description, and photo category and then call the routines called by the Append macro to insert the data into the spreadsheet. The end result is that we can add an image, a photo category, and photo description via code eliminating the need for the user to manually select the photo, category, and enter a description.
This is the method to add the photo in our VB.NET code:
Public Shared Sub RunExcelMacro(xlApp As Microsoft.Office.Interop.Excel.Application, macroName As String, arg1 As Object, arg2 As Object, arg3 As Object)
xlApp.Run(macroName, arg1, arg2, arg3, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
End Sub
and it would be called using this code:
RunExcelMacro(xlApp, "wksPhotos.PhotoAppend2", fileName, photoCategory, photoDescription) where fileName is a path to a .JPG file, photoCategory is one of the Photo Categories, and photo Description is a string.
The issue we're facing is that running the macro using OLE automation is extremely slow as up to 50 photos may be entered.
Is it possible to rewrite this macro using EPPlus exclusively? The end result is that there would be no difference between adding a photo programatically using EPPlus vs a user manually adding a photo via the Add Photo button.
The deliverable for this project would be a sample EPPlus program written in C# that adds 50 photos, categories, and description. The photos can be any image, the categories must be one of the categories in the Choose Photo Category modal, and the description can be a random set of characters.
This is the method to add the photo in our VB.NET code:
Public Shared Sub RunExcelMacro(xlApp As Microsoft.Office.Interop.Excel.Application, macroName As String, arg1 As Object, arg2 As Object, arg3 As Object)
xlApp.Run(macroName, arg1, arg2, arg3, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
End Sub
and it would be called using this code:
RunExcelMacro(xlApp, "wksPhotos.PhotoAppend2", fileName, photoCategory, photoDescription) where fileName is a path to a .JPG file, photoCategory is one of the Photo Categories, and photo Description is a string.
The issue we're facing is that running the macro using OLE automation is extremely slow as up to 50 photos may be entered.
Is it possible to rewrite this macro using EPPlus exclusively? The end result is that there would be no difference between adding a photo programatically using EPPlus vs a user manually adding a photo via the Add Photo button.
The deliverable for this project would be a sample EPPlus program written in C# that adds 50 photos, categories, and description. The photos can be any image, the categories must be one of the categories in the Choose Photo Category modal, and the description can be a random set of characters.