Create Excel VBA code and programmatically with C #

Question

I am in the middle of a simple method that saves my DataGridView in an Excel document (only 1 sheet) and also adds VBA code and a button to run the VBA code.

public void SaveFile(string filePath) { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); //Change Workbook-properties. ExcelApp.Columns.ColumnWidth = 20; // Storing header part in Excel. for (int i = 1; i < gridData.Columns.Count + 1; i++) { ExcelApp.Cells[1, i] = gridData.Columns[i - 1].HeaderText; } //Storing Each row and column value to excel sheet for (int row = 0; row < gridData.Rows.Count; row++) { gridData.Rows[row].Cells[0].Value = "Makro"; for (int column = 0; column < gridData.Columns.Count; column++) { ExcelApp.Cells[row + 2, column + 1] = gridData.Rows[row].Cells[column].Value.ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(filePath); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); } 

I have implemented only exporting DataGridView.

EDIT: Thanks to Joel, I could, using the right words, look again for a solution. I think this may be helpful . Could you please correct me or give a couple of tips on what I should look for.

Answer 1

I wrote a small example in which it adds a new button to an existing book, and then adds a macro that will be called when the button is clicked .

 using Excel = Microsoft.Office.Interop.Excel; using VBIDE = Microsoft.Vbe.Interop; ... private static void excelAddButtonWithVBA() { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlBook = xlApp.Workbooks.Open(@"PATH_TO_EXCEL_FILE"); Excel.Worksheet wrkSheet = xlBook.Worksheets[1]; Excel.Range range; try { //set range for insert cell range = wrkSheet.get_Range("A1:A1"); //insert the dropdown into the cell Excel.Buttons xlButtons = wrkSheet.Buttons(); Excel.Button xlButton = xlButtons.Add((double)range.Left, (double)range.Top, (double)range.Width, (double)range.Height); //set the name of the new button xlButton.Name = "btnDoSomething"; xlButton.Text = "Click me!"; xlButton.OnAction = "btnDoSomething_Click"; buttonMacro(xlButton.Name, xlApp, xlBook, wrkSheet); } catch (Exception ex) { Debug.WriteLine(ex.Message); } xlApp.Visible = true; } 

And here we got the buttonMacro(..) method

 private static void buttonMacro(string buttonName, Excel.Application xlApp, Excel.Workbook wrkBook, Excel.Worksheet wrkSheet) { StringBuilder sb; VBIDE.VBComponent xlModule; VBIDE.VBProject prj; prj = wrkBook.VBProject; sb = new StringBuilder(); // build string with module code sb.Append("Sub " + buttonName + "_Click()" + "\n"); sb.Append("\t" + "msgbox \"" + buttonName + "\"\n"); // add your custom vba code here sb.Append("End Sub"); // set an object for the new module to create xlModule = wrkBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule); // add the macro to the spreadsheet xlModule.CodeModule.AddFromString(sb.ToString()); } 

Found this information in an article in KB How to create an Excel macro using automation with Visual C # .NET