locked
write and execute VBA code via C# RRS feed

  • Question

  • More out of curiosity than anything, is it possible to write VBA code and implement it into Word or Excel and then have Word or Excel run that Macro, controlling everything through C#?

    I'm sure most things can be done with C# but at times it seems like VBA would work nicely with the interop and Office Tools libraries. 


    Wednesday, October 8, 2008 6:05 PM

Answers

  • Yes.  There is an example somewhere on Micrsoft.com.  Search for it.  (It might be a VB.Net example, but same thing.)   You could use the technique to minimize context switches.  Each call to an Office program from .NET results in a monstrous time hit.
    • Edited by JohnWein Wednesday, October 8, 2008 9:39 PM
    • Marked as answer by Harry Zhu Monday, October 13, 2008 2:50 AM
    Wednesday, October 8, 2008 9:36 PM
  • Thank you!  I was able to find some material on the subject.  It turned out to be even easier than I thought.  This is really cool stuff, especially since you can import the macro from a file from C# and then into VBA and then remove it as if it had never existed.


    For those curious in the future:

    Reference Microsoft Visual Basic for Applications Extensibility
    public void writeMacro() 
     
                //oMissing = System.Reflection.Missing.Value 
                //oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing) 
     
                Microsoft.Vbe.Interop.VBComponent vbModule = oDoc.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); 
                string macroCode = "sub autoMacro()\r\n msgbox \" I am a macro. \" \r\n end sub"
                vbModule.CodeModule.AddFromString(macroCode); 
     
                oDoc.Application.Run("autoMacro"ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing); 
     
                vbModule = null
     


    • Marked as answer by Harry Zhu Monday, October 13, 2008 2:51 AM
    Friday, October 10, 2008 7:33 PM

All replies

  • Yes.  There is an example somewhere on Micrsoft.com.  Search for it.  (It might be a VB.Net example, but same thing.)   You could use the technique to minimize context switches.  Each call to an Office program from .NET results in a monstrous time hit.
    • Edited by JohnWein Wednesday, October 8, 2008 9:39 PM
    • Marked as answer by Harry Zhu Monday, October 13, 2008 2:50 AM
    Wednesday, October 8, 2008 9:36 PM
  • Thank you!  I was able to find some material on the subject.  It turned out to be even easier than I thought.  This is really cool stuff, especially since you can import the macro from a file from C# and then into VBA and then remove it as if it had never existed.


    For those curious in the future:

    Reference Microsoft Visual Basic for Applications Extensibility
    public void writeMacro() 
     
                //oMissing = System.Reflection.Missing.Value 
                //oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing) 
     
                Microsoft.Vbe.Interop.VBComponent vbModule = oDoc.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); 
                string macroCode = "sub autoMacro()\r\n msgbox \" I am a macro. \" \r\n end sub"
                vbModule.CodeModule.AddFromString(macroCode); 
     
                oDoc.Application.Run("autoMacro"ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing); 
     
                vbModule = null
     


    • Marked as answer by Harry Zhu Monday, October 13, 2008 2:51 AM
    Friday, October 10, 2008 7:33 PM