Having hard time positioning pictures in Excel cell with vba

Last two days I had a hard time trying to figure out why my vba macro failed to position pictures in cells.
My goal was to add pictures of products loaded from external JPG files (previously resized to 64×64) to each row, so user can easily distinguish the product she is working with just by glancing.
To emmbed pictures into the file I used Shapes.AddPicture method. To properly position it within the cell I passed Left and Top paramters.

Set shp = targetSheet.Shapes.AddPicture( _
                    fileName:=picPath, LinkToFile:=False, SaveWithDocument:=True _
                    , Left:=picLeft, Top:=picTop, Width:=-1, Height:=-1)

By passing -1 to Width and Height parameters the picture preserved it’s original dimensions.

But no matter what parameters I passed to AddPicture method each next product was placed with a little offset from the top of the target cell. And each next product it was misplaced a little bit further to the bottom.
I was almost out of ideas when I understood that the zoom of the target sheet was set to 80, as it’s more was required for this report.
After setting it to 100 in vba macro before inserting pictures and then resetting it back to 80 I was able to properly position images within cells.
So here’s a little chunk of code that inserts pictures for me.

appWorkbooks.OpenText fileName:=m_picsDataFilePath, TextQualifier:=xlNone, DataType:=xlDelimited, Tab:=True, _
    FieldInfo:=Array( _
        Array(1, xlGeneralFormat_), Array(2, xlGeneralFormat_), Array(3, xlGeneralFormat_) _
Set picsWorkbook = appWorkbooks(GetFName(m_picsDataFilePath))

ActiveWindow.Zoom = 100

plog ("Insert pictures")
picColumnIdx = m_targetDataMapper.GetIdx("V41")
Set workRange = targetSheet.Cells(1, picColumnIdx)
workRange.EntireColumn.NumberFormat = "0"
picLeft = workRange.Left + 2

Set workRange = Nothing

Set sourceSheet = picsWorkbook.Sheets(1)

rowIdx = templateRow + 1
maxRowIdx = templateRow + sourceRowCount
picRowIdx = 2

Do While (rowIdx <= maxRowIdx) ' And rowIdx < 100
    picPath = sourceSheet.Cells(picRowIdx, 1).value
    picTop = 0
    picTop = targetSheet.Cells(rowIdx, picColumnIdx).Top + 2
    ' plog (CStr(picLeft) & " " & CStr(picTop))
    If "" <> picPath Then
        ' picWidth = sourceSheet.Cells(picRowIdx, 2).value
        ' picHeight = sourceSheet.Cells(picRowIdx, 3).value

        Set shp = targetSheet.Shapes.AddPicture( _
            fileName:=picPath, LinkToFile:=False, SaveWithDocument:=True _
            , Left:=picLeft, Top:=picTop, Width:=-1, Height:=-1)
        shp.Placement = xlMoveAndSize
        Set shp = Nothing
        Set shp = targetSheet.Shapes.AddShape(msoShapeRectangle, _
            picLeft, picTop, 65, 68)
        With shp.Fill
            .ForeColor.RGB = RGB(255, 255, 255)
            .BackColor.RGB = RGB(255, 255, 255)
        End With
        shp.Line.Visible = msoFalse
        shp.Placement = xlMoveAndSize
        Set shp = Nothing
    End If
    picRowIdx = picRowIdx + 1
    rowIdx = rowIdx + 1
picsWorkbook.Close SaveChanges:=False
Set picsWorkbook = Nothing
ActiveWindow.Zoom = 80

Note the addition of the white rectangular shape for each missing picture to fully cover the cell. It solves the problem of stacking pictures when somebody filters products.
I was able to solve this problem, but I wonder how many other quirks does Excel have.

Solving AttributeError: ‘SFTP’ object has no attribute ‘proc’

Seems that great SFTP plugin for Sublime text 2 has some trouble downloading empty folder from server with resulting errors in log file:

2016-09-03 20:37:33
Traceback (most recent call last):
  File "./sftp/threads.py", line 16, in run_with_except_hook
  File "./sftp/threads.py", line 119, in handler
  File "./sftp/commands.py", line 355, in run
  File "./sftp/commands.py", line 510, in do_operation
  File "./sftp/file_transfer.py", line 47, in handler
  File "./sftp/file_transfer.py", line 519, in listr
  File "./sftp/sftp_transport.py", line 322, in close
AttributeError: 'SFTP' object has no attribute 'proc'

To bypass it just add some file to your folder and then download it via SFTP.

Installing xrdb on Cent OS 6.4

I have been struggling to install xrdb on Cent OS 6.4
That’s what helped.

Add http_caching=packages to /etc/yum.conf
yum clean all
rm -fr /var/cache/yum/*
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
rpm -ivh epel-release-latest-6.noarch.rpm
sudo yum upgrade ca-certificates --disablerepo=epel
yum install xrdp
yum install tigervnc
yum install tigervnc-server
chkconfig --levels 35 xrdp on
service xrdp start

Many thanks to the Internet ^)

Making myself a backdoor through the corporate firewall. Again.

Recently I’ve been fighting with corporate internet access policy again.
After recent updates Firefox and Palemoon (my browser of choice) stopped working with ForceBindIP utility. Period.
So I’ve starged using VMWare CentOS virtual machine with virtual network adapter bound to my WiFi physical adapter connected to cell phone hotspot. Not so convenient but at least it works.
ForceBindIP is still usefull for connecting to blocked servers with some utilities say WinSCP.

Python: implications while with applying regexp over UTF-16 file and the example of how to solve them.

This simple Python program helps to quickly extract distinct Essbase errors from given log file. It demonstrates the usage of ArgumentParser, the usage of dictionaries and sets, reading of UTF-16 file (with BOM) and applying unicode regex on it’s contents. Plus it parses itself to find all Essbase error codes. The actual file is pretty large, because it contains all error codes in the final section, but it was shortened for this blog.
For me the hard part was to actually read UTF-16 file and not to forget adding `re.UNICODE` to `re.compile` call.

# -*- coding: utf-8 -*-
# distinct_ess_errors.py

import sys
import os
import io
import re
import codecs
from argparse import ArgumentParser

usage = "usage: %prog "
parser = ArgumentParser()

arg_group = parser.add_mutually_exclusive_group()
parser.add_argument( "log_file"
	, help = "Error or log file" )
parser.add_argument( "pattern"
	, help = "Pattern" )

( args ) = parser.parse_args()

if not args.log_file  or not args.pattern:
	print "Not all parameters set"
	sys.exit( 0 )

messageFinder = re.compile( r"%%(\d+?)\s(.*)$" )
fmsg = open( 'distinct_ess_errors.py' )
msg = {}
msgCount = 0
for line in fmsg:
	match = messageFinder.search( line )
	if None <> match:
		msg[ match.group(1) ] = match.group(2)
		msgCount = msgCount + 1
print "Parsed " + str( msgCount ) + " messages"

finder = re.compile( u"" + args.pattern, re.UNICODE )
distinct = set()

lineCount = 0
if args.log_file and os.path.isfile( args.log_file ):
	fin = io.open(args.log_file, encoding='utf-16')
	for line in fin:
		lineCount = lineCount + 1
		match = finder.search( line )
		if ( None <> match ):
			if not( match.group(1) in distinct ):
				distinct.add( match.group(1) )

print "Searched " + str( lineCount ) + ' lines for "' + args.pattern + '"'
print "\nFound\n"
for el in distinct:
	print el + " " + msg[el]
sys.exit( 0 )

#Created: Jan 13 2015 23:14:17
%%1001000 Unable to Open Report File [%s] on Server
%%1001001 Unknown Command [%s] in Report
%%1001002 Incorrect Syntax for Range Format in Report

Remove BOM with the help of Python before concatenating files

One day I was struggling to concatenate files generated by 3rd party utility for Essbase upload.
Uploading did broke on every single run and I cound not find the culprit until I opened the outline file with HxD HEX editor and found that extra bytes were added between each concatenated file.
To my suprise the 3rd party utility unloaded data from database in Unicode with BOM starting each separate file.
So before concatenating those files I had to remove BOM from them.
Thanks to this SO answer it was easily achievable with this python script.

# -*- coding: utf-8 -*-
import os, sys, codecs

BUFSIZE = 4096
BOMLEN = len(codecs.BOM_UTF8)

path = sys.argv[1]
with open(path, "r+b") as fp:
    chunk = fp.read(BUFSIZE)
    if chunk.startswith(codecs.BOM_UTF8):
        i = 0
        chunk = chunk[BOMLEN:]
        while chunk:
            i += len(chunk)
            fp.seek(BOMLEN, os.SEEK_CUR)
            chunk = fp.read(BUFSIZE)
        fp.seek(-BOMLEN, os.SEEK_CUR)

Oracle: Iterate over regexp matches with hierarchical query trick

  for matches in ( 
    with in_data as (
      select 'v113*(v43|v42|v900)/v54' haystack
        , 'v\d+' needle
      from dual
    , matches ( a_match, occ ) as ( 
      select regexp_substr( haystack, needle, 1, 1 ) a_match
        , 1 occ
      from in_data
      union all
      select regexp_substr( haystack, needle, 1, p.occ+1 ) a_match
        , p.occ + 1 as occ
      from matches p
      cross join in_data
      where p.a_match is not null
    cycle a_match set cycle to 1 default 0
    select a_match
    from matches
    where a_match is not null
  ) loop
    dbms_output.put_line( matches.a_match );
  end loop;