Wednesday, March 21, 2018

Apply traffic light icons to a custom range of cells in Excel using VBA


Sub iconsets0()
Dim rg As Range
Dim iset As IconSetCondition
Dim positiveCells As Range
Dim negativeCells As Range
'observed range:
Set rg = Range("C7", "G25")
rg.FormatConditions.Delete
For Each c In rg
    If c.Value >= 0 Then
        If Not positiveCells Is Nothing Then
            Set positiveCells = Union(positiveCells, c)
        Else
            Set positiveCells = c
        End If
    End If
    If c.Value < 0 Then
        If Not negativeCells Is Nothing Then
            Set negativeCells = Union(negativeCells, c)
        Else
            Set negativeCells = c
        End If
    End If
Next c

' apply to cells with positive number values:
Set positiveSet = positiveCells.FormatConditions.AddIconSetCondition

With positiveSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = True
    .ShowIconOnly = False
End With
With positiveSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "10"
End With
With positiveSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "25"
End With

' apply to cells with negative number values:
Set negativeSet = negativeCells.FormatConditions.AddIconSetCondition

With negativeSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = False
    .ShowIconOnly = False
End With
With negativeSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-25"
End With
With negativeSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-10"
End With


End Sub



Thursday, March 15, 2018

To install posh-gvm

1. Set JAVA_HOME to the root directory of the JDK.
2. Run this before invoking gvm:
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Friday, February 23, 2018

Importing a PKCS12 keystore into a JKS keystore

Wow, I did not know keytool can actually do this.

keytool -importkeystore -destkeystore keystore.jks -srckeystore keystore.p12 -alias tomcat

Via Jim Connor's Blog.

Wednesday, February 21, 2018

Grails: adjust Hibernate settings to prevent "org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement"

Grails 3.3.2 project. GORM, Hibernate, JTDS (with Microsoft SQL Server). I kept getting the following exception:


Error executing DDL via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:524)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:470)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:273)
        at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:203)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:65)
        at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:478)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:422)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:711)
        at org.grails.orm.hibernate.cfg.HibernateMappingContextConfiguration.buildSessionFactory(HibernateMappingContextConfiguration.java:274)
        at org.grails.orm.hibernate.connections.HibernateConnectionSourceFactory.create(HibernateConnectionSourceFactory.java:86)
        at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:39)
        at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:23)
        at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:64)
        at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:52)
        at org.grails.datastore.mapping.core.connections.ConnectionSourcesInitializer.create(ConnectionSourcesInitializer.groovy:24)
        at org.grails.orm.hibernate.HibernateDatastore.(HibernateDatastore.java:196)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.springsource.loaded.ri.ReflectiveInterceptor.jlrConstructorNewInstance(ReflectiveInterceptor.java:1076)
        at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:142)
(...)

Adding the default_catalog: (name of the database here) and default_schema: (name of the schema, usually dbo) underneath the hibernate: section in application.yml solved the issue.

Monday, February 12, 2018

Stop+Disable and Make Automatic+Start Windows Services using PowerShell.

When you need to make sure all Cisco services (Windows services) are stopped and set to manual but you need to do this on 20 servers and there's at least 10 services per server then you start doing things like this:

Stop and set Startup type to Manual:


Get-Service |Where-Object {($_.DisplayName -like 'Cisco*')} |foreach {Stop-Service $_.Name}
Get-Service |Where-Object {($_.DisplayName -like 'Cisco*')} |foreach {Set-Service $_.Name -StartupType Manual}
Set Startup type to Automatic and start:


Get-Service |Where-Object {($_.DisplayName -like 'Cisco*')} |foreach {Set-Service $_.Name -StartupType Automatic}
Get-Service |Where-Object {($_.DisplayName -like 'Cisco*')} |foreach {Start-Service $_.Name}