Excel Shortcut for VSTO SE Addin

I found it difficult to assign a shortcut key for VSTO SE Excel addin while I am working on my first project in VSTO. Then I got help from msdn forum and Zi Zhou - MSFT helped me to figure out the below described solution. Thanks a ton to him as it saved a lot of my time.

To assign shortcut key for VSTO SE addin from Excel 2007 is not possible and we have do some workaround to fix this. I am not sure about the VSTO v3 but for VSTO SE we have to follow the below given procedure.

We can’t get the button’s handle created from ribbon.xml file from VBA. We need to expose this COM addin to let VBA call the function. Let’s see how we can do this in the following steps:

Step 1: Create an Excel Addin named ExcelAddin using VSTO SE and Excel 2007.

Step 2: Add a ribbon support to the project and keep the default name (ribbon.xml)

Step 3: Ribbon call back would be something like this

Public Sub MyModule(ByVal control As Office.IRibbonControl)
Select Case control.Id
Case "BlueUL"
Globals.ThisAddIn._addinUtilities.BlueUL()
Case “SetPrintArea”
Globals.ThisAddIn._addinUtilities.SetPrintArea()
End Select
End Sub

Step 4: Add Interface to the project - Right Click on the Project --> Click Add New item --> Select “Interface” from the available templates and name it as “ExcelAddinInterface” and add the below code block.

Imports System.Runtime.InteropServices
<ComVisible(True)> _
<Guid("85B3534B-DD1A-4f07-B604-4EE610453F78")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface ExcelAddInterface
Sub BlueUL()
Sub SetPrintArea()
End Interface

Step 5: Add a class to the project – Right Click on the Project --> Click Add New item --> Select “Class” from the available templates and name it as “ExcelAddinClass” and add the below code block

Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports Microsoft.VisualBasic
<ComVisible(True)> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class LazAddinClass
Implements LazAddinInterface

Public Sub BlueUL() Implements LazAddinInterface.BlueUL
On Error Resume Next
With Globals.ThisAddIn.Application.Selection.
_Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 7
End With
End Sub

Public Sub SetPrintArea() Implements LazAddinInterface.SetPrintArea
Dim res As String
res = MsgBox("Are you sure to reformat the print area?",
MsgBoxStyle.YesNo)
If res = vbYes Then
Globals.ThisAddIn.Application.ActiveSheet.PageSetup. _
PrintArea = Globals.ThisAddIn.Application.Selection.Address
End If
End Sub
End Class

Step 6: In ExcelAddinClass we need to override the RequestComAutomationService() to return a new instance of the class we created above and make the field public, which makes the call in Step 3 can be compiled.

Public _addinUtilities As LazAddinClass
Protected Overrides Function RequestComAddInAutomationService() As Object
If (_addinUtilities Is Nothing) Then
_addinUtilities = New LazAddinClass
End If
Return _addinUtilities
End Function

We are done with the VSTO part and we have to create an Excel Addin to expose this procedure.

Step 7: Open Excel, Click Developer Tab --> Code Group --> Visual Basic. In Visual Basic editor, add a module and add the below code block

Dim addin As Office.COMAddIn
Dim automationObject As Object

Sub BlueUL()
Set addin = Application.COMAddIns("ExcelAddin")
Set automationObject = addin.Object
automationObject.BlueUL
End Sub

Sub SetPrintArea()
Set addin = Application.COMAddIns("ExcelAddin")
Set automationObject = addin.Object
automationObject.SetPrintArea
End Sub

Sub Auto_Open()
Application.OnKey "+^{F3}", "BlueUL"
Application.OnKey "+^{F4}", "SetPrintArea"
End Sub

Save the workbook as an Addin (“ExcelShortcut.xlam”)

Step 8: Click Office button --> Excel Options --> Add-ins tab, Choose Excel Add-ins, click Browse and select “ExcelShortcut.xlam” file and click OK

Step 9: Now goto VSTO addin, compile and run the project. Press “Ctrl+Shift+F3”. The bottom of the selected cells will be changed to pink color

Please let me know if you face any difficulty in this process.

Reading XML from VBA

I have faced with this problem (reading XML file from VBA and populate these values in a PowerPoint 2000 template) for quite sometime and I thought it is not possible to read XML file from Office 2000 (which later I came to know is possible). Also, I didn’t read much of the article, as I know we can open XML file in Excel 2003. So I made up myself to read this XML file via Excel and then populate these values in PowerPoint, which I successfully did.

But later I found we can add an XML reference and can read the XML file easily from Word, Excel or PowerPoint. So here I am providing an example on how to read XML file from PowerPoint.

Our XML file has the below structure. If you want to test it, copy the Code below and save it as "Employee.XML"

Employee.XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<EmpDetails>
<Employee>
<Name>ABC</Name>
<Dept>IT-Software</Dept>
<Location>New Delhi</Location>
</Employee>
<Employee>
<Name>XYZ</Name>
<Dept>IT-Software</Dept>
<Location>Chennai</Location>
</Employee>
<Employee>
<Name>IJK</Name>
<Dept>HR Operations</Dept>
<Location>Bangalore</Location>
</Employee>
</EmpDetails>

Open up VBA editor in PowerPoint and add references to "Microsoft XML, v3.0" (this reference is for Office 2000).

Create a new module and copy paste the following code and run it.

Below is the code to read the above XML file:

Sub XMLfromPPTExample()
Dim XDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xEmployee As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode

Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load ("C:\Emp.xml")
Set xEmpDetails = XDoc.documentElement
Set xEmployee = xEmpDetails.firstChild
For Each xEmployee In xEmpDetails.childNodes
For Each xChild In xEmployee.childNodes
MsgBox xChild.baseName & " " & xChild.Text
Next xChild
Next xEmployee
End Sub