本文介绍了有没有办法添加vba宏代码到excel?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个现有的宏函数有问题,所以我要做的是将一个宏传递给excel我的意思是添加函数(Ex:a())到excel,并运行函数(a() )添加。



注意:我有excel的路径,我必须添加宏。请清楚答案。



如何做?



提前感谢。 >

解决方案


  • 安装主互操作程序集合可用于您的办公室版本

  • 添加对Assemblies的引用 - > Extensions - > Microsoft.Office.Interop.Excel和Microsoft.VBE.Interop

      using System ; 
    使用System.Diagnostics;
    使用System.Windows.Forms;
    使用Microsoft.Vbe.Interop;
    使用ExcelInterop = Microsoft.Office.Interop.Excel;

    命名空间WindowsFormsApplication1
    {
    public partial class AddExcelMacro:Form
    {
    public AddExcelMacro()
    {
    InitializeComponent() ;
    AddMacro();
    }

    public void AddMacro()
    {
    try
    {
    //打开excel文件
    const string excelFile = @ C:\temp\VBA\test.xlsm;
    var excelApplication = new ExcelInterop.Application {Visible = true};
    var targetExcelFile = excelApplication.Workbooks.Open(excelFile);

    //将标准模块添加到文件
    var newStandardModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
    var codeModule = newStandardModule.CodeModule;

    //将vba代码添加到模块
    var lineNum = codeModule.CountOfLines + 1;
    var macroName =Button1_Click;
    var codeText =Public Sub+ macroName +()+\r\\\
    ;
    codeText + =MsgBox \嗨来自Excel \+\\\\
    codeText + =End Sub;

    codeModule.InsertLines(lineNum,codeText);
    targetExcelFile.Save();

    //运行宏
    var macro = string.Format({0}!{1}。{2},targetExcelFile.Name,newStandardModule.Name,macroName);
    excelApplication.Run(宏);

    excelApplication.Quit();

    }
    catch(Exception ex)
    {
    Debug.WriteLine(ex.Message);
    throw;
    }
    }
    }
    }



I have a problem with a existing macro function,so what I have to do is to pass a macro to excel I mean add the function(Ex: a()) to excel,and run the function("a()") added.

Note:I have path of the excel where i`ll have to add macro into. Please be clear about the answers.

How to do this??

Thanks in advance.

解决方案

  • install Primary Interop Assemblies Redistributable for your office version
  • Add reference to Assemblies -> Extensions ->Microsoft.Office.Interop.Excel and Microsoft.VBE.Interop

    using System;
    using System.Diagnostics;
    using System.Windows.Forms;
    using Microsoft.Vbe.Interop;
    using ExcelInterop = Microsoft.Office.Interop.Excel;
    
    namespace WindowsFormsApplication1
    {
        public partial class AddExcelMacro : Form
        {
            public AddExcelMacro()
            {
                InitializeComponent();
                AddMacro();
            }
    
            public void AddMacro()
            {
                try
                {
                    // open excel file
                    const string excelFile = @"c:\temp\VBA\test.xlsm";
                    var excelApplication = new ExcelInterop.Application { Visible = true };
                    var targetExcelFile = excelApplication.Workbooks.Open(excelFile);
    
                    // add standart module to file
                    var newStandardModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                    var codeModule = newStandardModule.CodeModule;
    
                    // add vba code to module
                    var lineNum = codeModule.CountOfLines + 1;
                    var macroName = "Button1_Click";
                    var codeText = "Public Sub " + macroName +"()" + "\r\n";
                    codeText += "  MsgBox \"Hi from Excel\"" + "\r\n";
                    codeText += "End Sub";
    
                    codeModule.InsertLines(lineNum, codeText);
                    targetExcelFile.Save();
    
                    // run the macro
                    var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newStandardModule.Name, macroName);
                    excelApplication.Run(macro);
    
                    excelApplication.Quit();
    
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                    throw;
                }
            }
        }
    }
    

这篇关于有没有办法添加vba宏代码到excel?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 17:06