Manipulate Visual Studio from Excel or Word

  • Article

You can use automation to manipulate various applications, like Word, Excel, Visual Studio, Visual FoxPro
Automation can make many tasks easier, including testing.
You can imagine something like a Word macro that launches Excel to create a concordance of the words in the current Document.

https://blogs.msdn.microsoft.com/calvin_hsia/2016/02/26/create-a-concordance-for-a-word-document-from-c-using-automation/

You can explore the Excel “Developer” menu by enabling it:
File->Options->Customize Ribbon->Check “Developer”
image 

Let’s insert a macro:
Start Excel, choose new document, hit Alt-F11, then Menu->Insert->Module
Paste in this code:

Sub t()
Set ox = CreateObject("Word.application")
ox.Visible = 1
ox.documents.Add
ox.Selection = "Text inserted from Excel"
End Sub

Now hit F5 to run the macro

You can turn on macro recording and execute a few steps to see what kind of commands are available.

Similarly, you can start Word to create a new document, hit Alt-F11, etc.

You may ask yourself How does this work?

If you start RegEdit you can see the kinds of things you can create (Called Com Servers. You can easily create your own in FoxPro or )
 

image

Using Visual Foxpro (which also has an object model and is likewise automatable),
ox = CREATEOBJECT("VisualStudio.Solution")
dte=ox.DTE
dte.Application.MainWindow.Visible=1
dte.Solution.Open("C:\Users\CalvinH\Source\Repos\Bounce\Bounce\Bounce.sln")

image

Try the same code in Excel or Word. (Excel & Word use VBA, which requires the “Set” command when assigning an object to a variable:
Sub t()
    Set ox = CreateObject("VisualStudio.Solution")
    Set dte = ox.dte
    dte.Application.MainWindow.Visible = 1
    dte.Solution.Open ("C:\Users\CalvinH\Source\Repos\Bounce\Bounce\Bounce.sln")
End Sub

FoxPro intellisense is nice here because it shows the available members/properties and it shows help information in a tooltip
How did I know this? I wrote the code for FoxPro intellisense.

It used to be I could create “VisualStudio.Application”, but thtat doesn’t seem to work any moer

Does this work from other office applications like Outlook and PowerPoint? Try it and see!

See also:
Word Object Model: https://msdn.microsoft.com/en-us/library/kw65a0we.aspx
Excel Object Model: https://msdn.microsoft.com/en-us/library/wss56bz7.aspx
Visual Studio Object Model: https://msdn.microsoft.com/en-us/library/za2b25t3.aspx
https://blogs.msdn.microsoft.com/calvin_hsia/2006/07/14/create-a-net-usercontrol-that-calls-a-web-service-that-acts-as-an-activex-control-to-use-in-excel-vb6-foxpro/