Hi @Alien
To accsess Excel in Dynamo, you need use late binding to access the Excel API. This is because Dynamo is already accessing the Excel API. Challenges may arise when trying to access the API of another package, especially if Dynamo has already established a connection to it. This issue was solve by @mzjensen.
The below code to accses Excel by using late binding
public static string Set_Value_at_Range(string workbookPath,string sheetName, string Range, string Value,bool Visible, bool save,bool Close)
{
try
{
// Specify the file path and name of the Excel file
string filePath = workbookPath;
// Use late binding to create an instance of the Excel application
Type excelType = Type.GetTypeFromProgID("Excel.Application");
object excel = Activator.CreateInstance(excelType);
// Use late binding to open the Excel file
object workbooks = excelType.InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);
object workbook = workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, new object[] { filePath });
// Use late binding to access the first worksheet
object sheets = workbook.GetType().InvokeMember("Sheets", BindingFlags.GetProperty, null, workbook, null);
object sheet = sheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, sheets, new object[] { sheetName });
if (Visible)
{
excelType.InvokeMember("Visible", BindingFlags.SetProperty, null, excel, new object[] { true });
}
// Use late binding to add a value to cell "C3"
object range = sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, new object[] { Range });
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, new object[] { Value });
if (save)
{
// Use late binding to save the changes to the Excel file
workbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, workbook, null);
}
if (Close)
{
// Use late binding to close the Excel file and quit the application
workbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, workbook, null);
excelType.InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, null);
}
return "Done";
}
catch (Exception)
{
return "fail";
}
}
}
i use it in my pak. BriMohareb_XX to accsess excel and create some node like Run Macro from dynamo.
