Excel VBA - Misc File Handling Subroutines

Sub CopyFile()
    FileCopy "fullPATH\filename1", "fullPATH\filename2"
End Sub

Sub renameFile()
    Name "fullPATH\oldFileName" As "fullPATH\newFileName"
End Sub

Copy With Destination Defined Makes Cleaner VBA Code

Following example code simplify the copy process by eliminating 4 line codes such as Range.copy, Sheet.Activate, Range.Select, and Activesheet.Paste.

    Range("B12:C17").Copy Destination:=Range("B30")

Even shorter:

    Range("B12:C17").Copy Range("B30")

Similarly, following code can be used for moving:

    Range("B12:C17").Cut Range("B30")

Excel VBA - Delete All Blank Lines in Work Sheet

Following subroutine deletes all blank lines from current work sheet from the bottom with the range defined A1:Z50.

Sub DeleteRowsFromBottom()
Dim range As range, rows As Integer, i As Integer
  
Set range = ActiveSheet.range("A1:Z50")
rows = range.rows.Count
For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(range.rows(i)) = 0 Then range.rows(i).Delete
Next

End Sub

Save Current Excel Workbook To A CSV Format File

Following code can be used to save the current work book into CSV format to the folder fullPATH and ignore the backup.

ActiveWorkbook.SaveAs Filename:= _
        "fullPATH\fileName.csv", FileFormat:= _
        xlCSV, CreateBackup:=False

Excel VBA - Search Match Text

Following subroutines can be used to search a word "pattern" in column B and assume total row is 50.

This search from top:

Sub topDownSearch()
Dim lastRow As Integer
Dim curRow As Integer

lastRow = 50

For curRow = 1 to lastRow Step 1
    If Cells(curRow, 2) = "pattern" Then
        Cells(curRow, 2).Select
        MsgBox (Cells(curRow, 2))
     End If
Next

End Sub

This search from bottom:

Sub bottomUpSearch()
Dim lastRow As Integer
Dim curRow As Integer

lastRow = 50

For curRow = lastRow To 1 Step -1
    If Cells(curRow, 2) = "pattern" Then
        Cells(curRow, 2).Select
        MsgBox (Cells(curRow, 2))
    End If
Next

End Sub

Install Jenkins and Plugins/Tools for PHP5 CI on Ubuntu 14.04

You have to be a super user to perform the following steps.

Install Jenkins:
wget -q -O - http://pkg.jenkins-ci.org/debian/jenkins-ci.org.key | apt-key add -
echo deb http://pkg.jenkins-ci.org/debian binary/ > /etc/apt/sources.list.d/jenkins.list
apt-get update
apt-get install jenkins

Go to http://localhost:8080/ with a browser to make sure Jenkins is up and running.

Install Plugins:
wget http://localhost:8080/jnlpJars/jenkins-cli.jar
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin checkstyle
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin clover
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin dry
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin htmlpublisher
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin jdepend
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin plot
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin pmd
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin violations
 java -jar jenkins-cli.jar -s http://localhost:8080 install-plugin xunit
 java -jar jenkins-cli.jar -s http://localhost:8080 safe-restart

Download Tools:
wget https://phar.phpunit.de/phpunit.phar
wget https://squizlabs.github.io/PHP_CodeSniffer/phpcs.phar
wget https://squizlabs.github.io/PHP_CodeSniffer/phpcbf.phar
wget https://phar.phpunit.de/phploc.phar
wget http://static.pdepend.org/php/latest/pdepend.phar
wget https://phar.phpunit.de/phpcpd.phar
wget http://phpdox.de/releases/phpdox.phar
curl -s http://getcomposer.org/installer | php
chmod +x *.phar

Install and Verify:
mv phpunit.phar /usr/local/bin/phpunit
 phpunit --version
      PHPUnit 4.4.0 by Sebastian Bergmann.

mv phpcs.phar /usr/local/bin/phpcs
 phpcs --version
PHP_CodeSniffer version 2.0.0 (stable) by Squiz (http://www.squiz.net)

mv phpcbf.phar /usr/local/bin/phpcbf
 phpcbf --version
  PHP_CodeSniffer version 2.0.0 (stable) by Squiz (http://www.squiz.net)

mv phploc.phar /usr/local/bin/phploc

 phploc --version
  phploc 2.0.6 by Sebastian Bergmann.

mv pdepend.phar /usr/local/bin/pdepend

 pdepend --version
  PDepend 2.0.4

mv phpcpd.phar /usr/local/bin/phpcpd
 phpcpd --version
  phpcpd 2.0.1 by Sebastian Bergmann.

mv phpdox.phar /usr/local/bin/phpdox

 phpdox --version
  phpDox 0.7.0 - Copyright (C) 2010 - 2014 by Arne Blankerts

mv composer.phar /usr/local/bin/composer
 composer --version
  Composer version 1.0-dev (4cc52afcc4debecf4fa7ce998198355fea98e656) 2014-12-13 18:54:43

Fun with Bash Brace

Following results are tested on bash version 4.1.2.

1. Brace Expansion:

$ echo @{a,b,c,d}
@a @b @c @d

$ echo @{a,b,c,d}@
@a@ @b@ @c@ @d@

$ echo {5..12}
5 6 7 8 9 10 11 12

$echo {01..10}
01 02 03 04 05 06 07 08 09 10

$ echo 1.{0..9}
1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9

$ echo --{A..E}--
--A-- --B-- --C-- --D-- --E--

$ echo {A..C}{0..9}
A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 C0 C1 C2 C3 C4 C5 C6 C7 C8 C9

$ echo {{A..F},{a..e}}
A B C D E F a b c d e

$ echo {1..10..2}
1 3 5 7 9
* 2 is increment value in above example

2. Parameter Substitution:

Length of variable ${#var}:

$ abc=bash
$ echo ${#abc}
4

Remove pattern (front) ${var#Pattern}:

$ f="/etc/resolv.conf"
$ echo ${f#/etc/}
resolv.conf

Remove pattern (back) ${var%pattern}:

$ FILE="xcache-1.3.0.tar.gz"
$ echo ${FILE%.tar.gz}
xcache-1.3.0

Find and Replace ${varName/Pattern/Replacement}:

$ x="Use unix or die"
$ echo "${x/unix/linux}"
Use linux or die

Following is a reference for bash parameter substitution operators:

Prompting for user input with read in bash script

Following bash script demonstrates two different ways to get user's input. One is echo & read and other combines both with read -p option.

#!/bin/bash

# echo and read
echo -n "Enter your name: "
read name
echo "How are you, $name?"

# read -p option
read -p "Enter your name: " name
echo "How are you, $name?"

# read -p with multiple input values
read -p "Enter three numbers: " x y z
echo "$x + $y + $z = $(( x + y + z ))"

Following is the "read" documentation as reference:

read [-ers] [-u fd] [-t timeout] [-a aname] [-p prompt] [-n nchars] [-d delim] [name ...]
     
One line is read from the standard input, or from the file descriptor fd supplied as an argument to the -u option, and the first word is assigned to the first name, the second word to the second name, and so on, with leftover words and their intervening separators assigned to the last name.  If there are fewer words read from the input stream than names, the remaining names are assigned empty values. The characters in IFS are used to split the line into words. The backslash character (\) may be used to remove any special meaning for the next character read and for line continuation. Options, if supplied, have the following meanings:

-a aname
   The words are assigned to sequential indices of the array variable aname, starting at 0. aname is unset before any new values are assigned. Other name arguments are ignored.

-d delim
   The first character of delim is used to terminate the input line, rather than newline.

-e If the standard input is coming from a terminal, readline (see READLINE above) is used to obtain the line.

-n nchars
   read returns after reading nchars characters rather than waiting for a complete line of input.

-p prompt
   Display prompt on standard error, without a trailing newline, before attempting to read any input. The prompt is displayed only if input is coming from a terminal.

-r Backslash does not act as an escape character. The backslash is considered to be part of the line. In particular, a backslash-newline pair may not be used as a line continuation.

-s Silent mode. If input is coming from a terminal, characters are not echoed.

-t timeout
   Cause read to time out and return failure if a complete line of input is not read within timeout seconds.  This option has no effect if read is not reading input from the terminal or a pipe.

-u fd Read input from file descriptor fd.

If no names are supplied, the line read is assigned to the variable REPLY. The return code is zero, unless end-of-file is encountered, read times out, or an invalid file descriptor is supplied as the argument to -u.

Handle multiple return values in bash

Some times a command may return multiple values such as following example:

snmpwalk -v2c -c public 10.0.1.1 load
UCD-SNMP-MIB::laLoadFloat.1 = Opaque: Float: 0.260000
UCD-SNMP-MIB::laLoadFloat.2 = Opaque: Float: 0.110000
UCD-SNMP-MIB::laLoadFloat.3 = Opaque: Float: 0.030000

Following methods can be used to handle this case and print out 1, 5, and 15 minutes load in bash, and all of them have the identical output.

1. Set positional parameters:
load=`snmpwalk -v2c -c public 10.0.1.1 load | awk '{print $5}'`
set $load
printf '%-8s %7s %7s %10s\n' "Load:" $1 $2 $3

Note: Another way to assign return value to load
load=$(snmpwalk -v2c -c public 10.0.1.1 load | awk '{print $5}')

2. Multiple variable assignment:
read load_1 load_5 load_15 <<< `snmpwalk -v2c -c public 10.0.1.1 load | awk '{print $5}'`
printf '%-8s %7s %7s %10s\n' "Load:" $load_1 $load_5 $load_15

3. Array:
load=(`snmpwalk -v2c -c public 10.0.1.1 load | awk '{print $5}'`)
printf '%-8s %7s %7s %10s\n' "Load:" ${load[0]} ${load[1]} ${load[2]}