Getting technical

Automatically taken from my wordpress.com blog.


WiX Installer for VSTO with Visual Studio

Thu, 05 Sep 2019

I needed to create a silent installer for a VSTO Office Add-In I had created. The installer would be deployed silently via a group policy with an internal network. To do this an MSI (Microsoft Installer) would be ideal which would let you run something like this:

msiexec /i c:\path\to\installer.msi /quiet /log c:\path\to\info.log 

The only MS info I could find regarding the install of a VSTO was really old (originally for Visual Studio 2013) and required InstallShield.

I then investigated creating a MS “Setup Project” – which may have worked eventually, if I persevered, but there seemed to be a lot of “working out” to do that should be straight forward.

So, then I turned my attention to WiX Toolset. I had seen this used previously, it is free and I have nothing to lose.

I am currently using Visual Studio 2019 and these are the steps I went through just to install the WiX VS 2019 extension, which enabled me to even have the option of a WiX project.

I had problems installing the extension as I was missing a certificate (DigiCert High Assurance EV Root CA) for some unknown reason. This I found and installed.

I then followed the basics of this tutorial to create the WiX installer. However, I did make some minor alterations, such as customise the images used, removed the requirement to agree to T&Cs, automate the version numbers.

These are my custom variables defined within the project properties:

wix-vsto-vars

 

AddinFiles=..\AcmePowerPointAddIn\bin\$(Configuration)\;Resources=..\AcmePowerPointAddIn\Resources\

You also need to add references to you project:

WiX-vsto-refs

These can be browsed to here:

C:\Program Files (x86)\WiX Toolset v3.11\bin

I wanted to ensure .NET 4.5.2 or above was installed so I created the following condition:

 <PropertyRef Id="WIX_IS_NETFRAMEWORK_452_OR_LATER_INSTALLED" />
<Condition Message="This application requires .NET Framework 4.5.2 Please install the .NET Framework then run this installer again.">
<![CDATA[Installed OR WIX_IS_NETFRAMEWORK_452_OR_LATER_INSTALLED]]>
</Condition>

To remove the T&Cs I added the following:

<UI>
<UIRef Id="WixUI_InstallDir" />
<Publish Dialog="WelcomeDlg"
Control="Next"
Event="NewDialog"
Value="VerifyReadyDlg"
Order="2">1</Publish>
<Publish Dialog="VerifyReadyDlg"
Control="Back"
Event="NewDialog"
Value="WelcomeDlg"
Order="2">1</Publish>
</UI>

The following was added to incorporate an “Add/Remove” icon on the list of installed programs.

<Icon Id="icon.ico" SourceFile="$(var.Resources)icon.ico"/>
<Property Id="ARPPRODUCTICON" Value="icon.ico" />

And finally for the installer I added references for installer images – to replace the defaullt WiX images:

<WixVariable Id="WixUIBannerBmp" Value="$(var.Resources)sx-inst-banner.bmp" />
<WixVariable Id="WixUIDialogBmp" Value="$(var.Resources)sx-inst-back.bmp" />

And just in-case I forgot to mention something, here is my entire Product.wsx file:

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"
     xmlns:netfx="http://schemas.microsoft.com/wix/NetFxExtension">
  <Product Id="*" Name="Acme PowerPoint Connector" Language="1033"
           Version="!(bind.FileVersion.AcmePowerPointAddIn_dll)"
           Manufacturer="Acme" UpgradeCode="a9793455-a1d2-49b4-8085-1cc778aee0eb">
    <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />
    <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
  <Icon Id="icon.ico" SourceFile="$(var.Resources)icon.ico"/>
  <Property Id="ARPPRODUCTICON" Value="icon.ico" />
  <WixVariable Id="WixUIBannerBmp" Value="$(var.Resources)sx-inst-banner.bmp" />
  <WixVariable Id="WixUIDialogBmp" Value="$(var.Resources)sx-inst-back.bmp" />
    <Property Id="VSTORUNTIMEREDIST">
      <RegistrySearch
        Id="VSTORuntimeRedist"
        Root="HKLM"
        Key="SOFTWARE\Microsoft\VSTO Runtime Setup\v4R"
        Name="Version"
        Type="raw" />
    </Property>
    <Condition
      Message="The Visual Studio 2010 Tools for Office Runtime is not installed. 
  Please download and install from http://www.microsoft.com/en-us/download/details.aspx?id=20479.">
      <![CDATA[Installed OR VSTORUNTIMEREDIST>="10.0.30319"]]>
    </Condition>
    <PropertyRef Id="WIX_IS_NETFRAMEWORK_452_OR_LATER_INSTALLED" />
    <Condition Message="This application requires .NET Framework 4.5.2 Please install the .NET Framework then run this installer again.">
      <![CDATA[Installed OR WIX_IS_NETFRAMEWORK_452_OR_LATER_INSTALLED]]>
    </Condition>
    <Media Id="1" Cabinet="AcmePowerPointConnector.cab" EmbedCab="yes"/>
    <Feature Id="ProductFeature" Title="Acme PowerPoint Connector" Level="1">
      <ComponentGroupRef Id="ProductComponents" />
      <ComponentRef Id="Registry_FriendlyName" />
      <ComponentRef Id="Registry_Description" />
      <ComponentRef Id="Registry_Manifest" />
      <ComponentRef Id="Registry_LoadBehavior" />
    </Feature>
    <UI>
      <UIRef Id="WixUI_InstallDir" />
      <Publish Dialog="WelcomeDlg"
            Control="Next"
            Event="NewDialog"
            Value="VerifyReadyDlg"
            Order="2">1</Publish>
      <Publish Dialog="VerifyReadyDlg"
            Control="Back"
            Event="NewDialog"
            Value="WelcomeDlg"
            Order="2">1</Publish>
    </UI>
  </Product>
  <Fragment>
    <Directory Id="TARGETDIR" Name="SourceDir">
      <Directory Id="ProgramFilesFolder">
        <Directory Id="INSTALLFOLDER" Name="Acme PowerPoint Connector" />
        <Component Id="Registry_FriendlyName">
          <RegistryValue Id="RegKey_FriendlyName" Root="HKCU"
                         Key="Software\Microsoft\Office\PowerPoint\AddIns\Acme PowerPoint Connector"
                         Name="FriendlyName"
                         Value="Acme PowerPoint Connector"
                         Type="string" KeyPath="yes" />
        </Component>
        <Component Id="Registry_Description">
          <RegistryValue Id="RegKey_Description" Root="HKCU"
                         Key="Software\Microsoft\Office\PowerPoint\AddIns\Acme PowerPoint Connector"
                         Name="Description"
                         Value="Acme PowerPoint Connector"
                         Type="string" KeyPath="yes" />
        </Component>
        <Component Id="Registry_Manifest">
          <RegistryValue Id="RegKey_Manifest" Root="HKCU"
                         Key="Software\Microsoft\Office\PowerPoint\AddIns\Acme PowerPoint Connector"
                         Name="Manifest" Value="[INSTALLFOLDER]AcmePowerPointAddIn.vsto|vstolocal"
                         Type="string" KeyPath="yes" />
        </Component>
        <Component Id="Registry_LoadBehavior">
          <RegistryValue Id="RegKey_LoadBehavior" Root="HKCU"
                         Key="Software\Microsoft\Office\PowerPoint\AddIns\Acme PowerPoint Connector"
                         Name="LoadBehavior" Value="3"
                         Type="integer" KeyPath="yes" />
        </Component>
      </Directory>
    </Directory>
  </Fragment>
  <Fragment>
    <ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER">
      <Component Id="AcmePowerPointAddIn_vsto_Component">
        <File Id="AcmePowerPointAddIn_vsto" KeyPath="yes"
              Name="AcmePowerPointAddIn.vsto" Source="$(var.AddinFiles)"></File>
      </Component>
      <Component Id="AcmePowerPointAddIn_dll_manifest_Component">
        <File Id="AcmePowerPointAddIn_dll_manifest" KeyPath="yes"
              Name="AcmePowerPointAddIn.dll.manifest" Source="$(var.AddinFiles)"></File>
      </Component>
      <Component Id="MSOfficeToolsCommon_dll_Component">
        <File Id="MSOfficeToolsCommon_dll" KeyPath="yes"
              Name="Microsoft.Office.Tools.Common.v4.0.Utilities.dll"
              Source="$(var.AddinFiles)"></File>
      </Component>
      <Component Id="NewtonsoftJson_dll_Component">
        <File Id="NewtonsoftJson_dll" KeyPath="yes"
              Name="Newtonsoft.Json.dll"
              Source="$(var.AddinFiles)"></File>
      </Component>
      <Component Id="NewtonsoftJson_xml_Component">
        <File Id="NewtonsoftJson_xml" KeyPath="yes"
              Name="Newtonsoft.Json.xml"
              Source="$(var.AddinFiles)"></File>
      </Component>
      <Component Id="AcmePowerPointAddIn_dll_Component" >
        <File Id="AcmePowerPointAddIn_dll" KeyPath="yes"
              Name="AcmePowerPointAddIn.dll" Source="$(var.AddinFiles)" />
      </Component>
    </ComponentGroup>
  </Fragment>
</Wix>

MS SQL module for pyodbc

Wed, 12 Jun 2019

I’ve been learning Python over the past week, and following on from my previous post I put together a handy “data.py” module for all the basic SQL commands:

import pyodbc
import sys

from enum import Enum
class QueryType(Enum):
    INSERT = 1
    UPDATE = 2
    SELECT = 3
    FIRST = 4
    DELETE = 5

class Tsql:    
    def __init__(self, connstr):
        self.connstr = "DRIVER={ODBC Driver 17 for SQL Server};" + connstr #'DRIVER={SQL Server Native Client 17.0}; '
        
    def exe(self, queryType, query, vals = None):
        with pyodbc.connect(self.connstr) as connection:
            cursor = connection.cursor()
            try:
                if vals is None:
                    cursor.execute(query)
                else:
                    cursor.execute(query,vals)

                if queryType is QueryType.INSERT:
                    try:
                        if vals is None:
                            cursor.execute("SELECT SCOPE_IDENTITY()")
                        else:
                            cursor.execute("SELECT @@IDENTITY")
                        row = cursor.fetchone()
                        seed_id = row[0]
                    except AttributeError:
                        seed_id = 0
                        
                    cursor.commit()
                    return seed_id

                elif queryType is QueryType.SELECT:
                    rows = cursor.fetchall()
                    return rows

                elif queryType is QueryType.FIRST:
                    row = cursor.fetchone()
                    return row

                elif queryType is QueryType.UPDATE:
                    cursor.commit()
                    affected = cursor.execute("SELECT @@ROWCOUNT").fetchone()[0]
                    return affected                

                elif queryType is QueryType.DELETE:
                    cursor.commit()
                    affected = cursor.execute("SELECT @@ROWCOUNT").fetchone()[0]
                    return affected
                else:
                    print ("Query Type is not catered for!")
            
            except:
                err = sys.exc_info()
                print ("Error in SQL: ",err)

This module contains a class called Tsql. In theory you could add a MySql class and others.

To call this, you can do something like this (assuming you have a customers table and you specify your database connection string correctly):

import data as db
dbstr = 'SERVER=##DB_SERVER##;DATABASE=**DB_NAME**;UID=**DB_USER**;PWD=**DB_PASSWORD**'
tsql = db.Tsql(dbstr)
sql = "UPDATE customers SET name=? where name = ?"
vals = ("Fred","Bob")
affected = tsql.exe(db.QueryType.UPDATE, sql, vals)
print(affected)

Stored procedures need to be treated a little differently. The best approach I have seen was here:

https://stackoverflow.com/questions/50750101/pyodbc-read-output-parameter-of-stored-procedure-mssql

Also, if you want to get a DataFrame for Pandas from MS SQL, it can be done in this way (and you can get the column headers too):

https://tomaztsql.wordpress.com/2018/07/15/using-python-pandas-dataframe-to-read-and-insert-data-to-microsoft-sql-server/

 


Connect Python to MS SQL Server

Mon, 10 Jun 2019

In the event you need to connect to a MS SQL Server database from Python, there are two installs required to have Python connect to your MS SQL database.

Firstly download the latest ODBC connector from Microsoft and then, assuming you have Python installed using “pip”, run the following command:

pip install pyodbc

You should now just be able to import this, create a reference to the database which you can call and execute against:

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=**DB_SERVER**;DATABASE=**DB_NAME;UID=**DB_USER**;PWD=**DB_PASSWORD**')
db = conn.cursor()
db.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
db.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES;")
for x in db:
  print(x)

In the example above, the code above uses the latest ODBC connector for MS SQL Server to connect to an existing database, creates a customers table and then selects and lists all the tables found in the database.

 

 


PowerPoint VBA export slides to PPTX and JPG files

Fri, 24 May 2019

Normally I steer clear of VBA, but if I just need to do some quick processing then it makes sense!

Sub ExportSlidesToIndividual()
    Dim oPPT As Presentation, oSlide As Slide
    Set oPPT = ActivePresentation
    For Each oSlide In oPPT.Slides
        i = oSlide.SlideNumber
        t = "Slide"
         For Each oShp In oSlide.Shapes
          With oShp
            If .Type = msoPlaceholder Then
              Select Case .PlaceholderFormat.Type
                Case ppPlaceholderCenterTitle, ppPlaceholderTitle
                  t = oShp.TextFrame.TextRange.Text
                 
              End Select
            End If
          End With
        Next
        pad = Right(String(3, "0") & (i * 10), 3)
        oSlide.Export "c:\exported\" & pad & t & ".jpg", "JPG"
        oSlide.Export "c:\exported\" & pad &  t & ".pptx", "PPTX"            
    Next
    Set oPPT = Nothing
    MsgBox ("Processing complete")
End Sub


This code looks for the title of the slide and uses this as the filename for the image and PowerPoint file. A major issue with this code would be if a character was used in the title that was not allowed as a filename – but this is meant to be just a simple and quick.


Visual Studio Tools for Office with the Image Relay API

Tue, 21 May 2019

This project is just an example of what is possible with the Image Relay API. Image Relay is a DAM system. I have changed the client name and images for the purpose of this entry. It is also possible to do this using a demo account.

This add-in was created to allow users within an organisation to insert “on brand” imagery within a presentation.

I have done a few VSTO projects primarily for Word and PowerPoint. Once the VSTO add-in is deployed, then an extra tab is added to the ribbon and if the Acme button is clicked a web browser pane is shown. In this current version the browser is local to the user. All requests from the browser are sent via the VSTO code. This in turn uses the API and responses update the HTML document displayed. To avoid getting a security warning as the website opened, due to it running JavaScript, you need to trick the Web Browser control so it thinks the website has been copied from the local host machine.

You just stick this at the top of your base html file.

<!-- saved from url=(0016)http://localhost -->

Another useful trick is to tell the Web Browser control to use the latest version of the browser rendering engine available. In most cases this would be IE Edge or possibly IE 11, which do cater for most HTML5 controls. To do this you can either edit/add registry keys during install or have this line in the HTML, just after your head declaration:

<meta http-equiv="X-UA-Compatible" content="IE=edge" />

In this version, all the html, css and JavaScript is run from the file system directly and not via a web server. Any insertion of images into the document, or requests to Image Relay are controlled using the .NET VSTO code – the details of which are not covered in this post, as all MS Office products have different architectures and methods.

As an aside, originally when I investigated VSTO, I looked into the Web Add-in option and was disappointed to discover it was not possible to insert content into the current document. This is why I went the older VSTO route.