Excel-DNA add ins using ExcelCommand present in excel but doesn't execute the code.

[William Luna]

I am working on a project using dotnet that breaks pdfs up by grouped page numbers into different files. Part of this process I extract key data points such as the following: ID, contacts associated with the ID, path to the new pdf, number of pages. These four values are written to a specified sheet, this functionality works when running in visual studio as a console app but not when I try to call the code in excel with the project being a class library app. My next steps are to also incorporate emailing as a function and then add this to a separate button that would read the excel sheet for the necessary information. 

A little information I am developing on a mac but I am using visual studio within parallels, I am referencing the xll file help in bin/debug/net6.0-windows/excelUtilityClasses-Addin64.xll file. When I do launch excel from the code I do the errror in the screenshot attached. I have gone though all the macro and security settings to ensure permissions are in compliance to my needs.

Any help would be appreciated I have been stuck on this. Am I using ExcelCommand correctly? Are there some steps I can take to debug this further? Thank you

Here is my code:

using System;

using System.ComponentModel;

using System.Runtime.InteropServices;

using System.Text.RegularExpressions;

using System.IO;

using System.Collections.Generic;

using IronPdf;

using OfficeOpenXml;

using Microsoft.VisualBasic;

using ExcelDna.Integration;

namespace UnitilityServerClass

{

public static class MyFunctions

{

[ExcelCommand(MenuName = "MyFunctions", MenuText = "MyFunction1")]

public static void MyFunction1()

{

IronPdf.License.LicenseKey = "license";

string path = @"path";

string[] files = Directory.GetFiles(path, "*.pdf");

foreach (string file in files)

{

string parentFolderPath = @"path";

Directory.CreateDirectory(parentFolderPath);

// Load the PDF file

string company_pattern = @"pattern";

Match company = Regex.Match(file, company_pattern);

string company_string = company.Groups[1].Value;

var pdf = PdfDocument.FromFile(file);

string text = pdf.ExtractAllText();

string pattern_r = @"pattern";

MatchCollection matches_cpid = Regex.Matches(text, pattern_r); // extracts CPID for each page in order

List<string> cpid_list = new List<string>();

foreach (Match match in matches_cpid)

{

cpid_list.Add(match.Groups[1].Value);

}

int count = 0; // 'count' will represent page number we will need this for writing

List<int> index_lst = new List<int>(); // 'index_lst' will hold each page number

List<string> unique_lst = new List<string>(); // 'unique_list' will get unique CPID for matching

foreach (string cpid in cpid_list)

{

...

}

List<List<int>> list_of_pages_per_unique = new List<List<int>>(); // list that will be contained of lists that have the page numbers of repeated unique CPID resets each iteration of 'unique_lst'

foreach (string unique in unique_lst) // only iterate through unique_lst to verify we make 'list_of_pages_per_unique' same length as 'unique_lst'

{

...

}

string excelFilePath = @"excelPath";

// Define the worksheet where you want to write the data

using (var package = new ExcelPackage(excelFilePath))

{

ExcelWorksheet worksheet = package.Workbook.Worksheets["Split Information"];

string[] headers = { "ID", "Contacts","Path to Split File", "Page Count" };

for (int i = 0; i < unique_lst.Count; i++)

{

var pdf_extracted = pdf.CopyPages(list_of_pages_per_unique[i]);

string str = unique_lst[i];

string new_file_name = $"{str}_Statement.pdf";

string page_count = list_of_pages_per_unique[i].Count.ToString();

string filePath = Path.Combine(parentFolderPath, new_file_name);

pdf_extracted.SaveAs(filePath);

Dictionary<string, string> myDict = new Dictionary<string, string>

{

{ "ID", company_string},

{ "Contacts", str},

{ "Path to Split File", filePath},

{ "Page Count", page_count}

};

// Find the next available row in the worksheet

int nextRow = worksheet.Dimension?.End.Row + 1 ?? 1;

// Loop through the dictionary and write the values to the worksheet

foreach (KeyValuePair<string, string> kvp in myDict)

{

// Find the column index based on the header name

int columnIndex = Array.IndexOf(headers, kvp.Key) + 1;

// Write the value to the corresponding cell in the worksheet

worksheet.Cells[nextRow, columnIndex].Value = kvp.Value;

}

}

package.Save();

}

Console.WriteLine("File Processed");

}

//public void MyFunction2()

//{ Emailing function

}

}

}

[Govert van Drimmelen]

One error you show relates to the "file format and extension". This happens if you try to load the 32-bit add-in into a 64-bit version of Excel. I'm guessing you're running 64-bit Excel on the Parallels, and so the add-in loaded should be the -AddIn64.xll one.

It looks like the macro is being registered correctly when you show the menu with the MyFunctions -> MyFunction1 menu. Does the macro run correctly when you press this button? I think it should.

Finally, it looks like you are trying to bind the macro to a button on the sheet. For this you must use the "Form Controls" button and not the "ActiveX controls" button. Then in the Assign Macro dialog you type in the ExcelCommand name - so just "MyFunction1". Then when you press the button it should work and run the macro.

-Govert