Flip Between WorkBooks and write data

Question

I have a VSTO code that calls on a VBA code that is loaded onto the VSTO Ribbon Header. I was wanting to know if its possible (and how) you would be able to write a value from the VBA code onto the VSTO workbook. I have attached my code below.

using Microsoft.Office.Tools.Ribbon;
using System;

using Excel = Microsoft.Office.Interop.Excel;

namespace Isitpossible
{
    public partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {

        }

        private void callVBA_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook xlWorkBook;
            String FilenamePath;

            FilenamePath = "C:\Test\MyWorkbook.xlsm"; //Where the workbooks where all the marco are stored

            //~~> Start Excel and open the workbook.

            xlWorkBook = xlApp.Workbooks.Open(FilenamePath,0, true); 
            xlApp.Visible = false;


            //~~> Run the macros by supplying the necessary arguments
            xlApp.Run("Test");

        }
    }
}

My VBA sub is written here:

Sub Test()
ActiveWorkbook.ActiveSheet.Range("C1").Value = "This Works!"
End Sub

score 0 · Accepted Answer · answered Jan 09 '22 at 21:00

First of all, there is no need to create a new Application instance from your VSTO add-in:

Excel.Application xlApp = new Excel.Application();

To access the object model of the host application, use the Application field of the ThisAddIn class. This field returns an object that represents the current instance of the host application. Read more about that in the Access the object model of the host application section on MSDN.

So, your code should look like that:

        private void callVBA_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Workbook xlWorkBook;
            String FilenamePath;

            FilenamePath = "C:\Test\MyWorkbook.xlsm"; //Where the workbooks where all the marco are stored

            //~~> Start Excel and open the workbook.

            xlWorkBook = Application.Workbooks.Open(FilenamePath,0, true); 
            xlApp.Visible = false;


Application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;

            //~~> Run the macros by supplying the necessary arguments
            xlApp.Run("Test");

        }

See Run a Macro from C# for more information.

Flip Between WorkBooks and write data

1 Answers1