Blog‎ > ‎

IT


Save text to file in Excel VBA

posted Dec 29, 2013, 3:00 AM by Jaco Vosloo   [ updated Dec 29, 2013, 3:05 AM ]

This VBA script will save the text from one column (O) into a file for which the name is taken from column (K).
Surprisingly this method, which opens and closes a file for each row of the sheet is much faster than the method I describe here, which tells Excel to save sheets to file as csv. I presume the overhead is due to Excel having to serialise the sheets to CSV format.

Public Sub ExportPastelCSV()
On Error GoTo HandleErr

Dim ThisRow As Range
Dim OutputPath As String
Dim OutputFile As String
Dim OutputRow As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.CalculateFullRebuild

'Make a folder with the name of the worksheet
OutputPath = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) 'Simple but bad way to remove the extension.
OutputPath = ThisWorkbook.path & Application.PathSeparator & OutputPath
If Dir(OutputPath, vbDirectory) = "" Then
    MkDir OutputPath
Else
    If Dir(OutputPath & "\*.*") <> "" Then
        Kill OutputPath & "\*.*"
    End If
End If

Dim TheSheet As Worksheet
Set TheSheet = ThisWorkbook.ActiveSheet
Dim TheRange As Range
Set TheRange = ThisWorkbook.ActiveSheet.UsedRange

If TheRange.Rows.Count > 1 Then
   Set TheRange = TheRange.Resize(TheRange.Rows.Count - 1, TheRange.Columns.Count).Offset(1, 0)
 
    For Each ThisRow In TheRange.Rows
    OutputFile = ThisRow.Cells(1, 11).Value
        If OutputFile <> "" Then
            OutputRow = ThisRow.Cells(1, 15).Value
            OutputFile = OutputPath & Application.PathSeparator & OutputFile & ".csv"
            Open OutputFile For Append Lock Write As #1
            Write #1, OutputRow
            Close #1
        End If
    Next ThisRow
End If

MsgBox "Successfully exported the Pastel CSV files in a folder with the same name as this worksheet:" & vbCrLf & OutputPath & vbCrLf

Finally:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub

HandleErr:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
        "Source: " & Err.Source & " " & vbCrLf & _
        "Number: " & Err.Number & " " & vbCrLf & _
        "Description: " & Err.Description & " " & vbCrLf

GoTo Finally
End Sub

Save every sheet in an Excel workbook as a CSV file

posted Dec 28, 2013, 1:42 AM by Jaco Vosloo   [ updated Dec 28, 2013, 3:05 AM ]

Today I wanted to save every sheet in an Excel workbook as a CSV file, the headings of each sheet is removed first.
Here's the code:

Public Sub SaveAllSheetsAsCSV()
On Error GoTo HandleErr

Dim Sheet As Worksheet
Dim NewWorkbook As Workbook
Dim OutputPath As String
Dim OutputFile As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.CalculateFullRebuild

'Make a folder with the name of the worksheet
OutputPath = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) 'Simple but bad way to remove the extension.
OutputPath = ThisWorkbook.path & Application.PathSeparator & OutputPath
If Dir(OutputPath, vbDirectory) = "" Then
    MkDir OutputPath
End If

For Each Sheet In Sheets
    Set NewWorkbook = Workbooks.Add
    ' Copy contents without headers
    With Sheet.UsedRange
        If .Rows.Count > 1 Then
            .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy
        Else
            .Copy
        End If
    End With
    ' Paste values only
    NewWorkbook.Sheets.Item(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    ' Save to CSV file
    OutputFile = OutputPath & Application.PathSeparator & Sheet.Name & ".csv"
    NewWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
    NewWorkbook.Close
Next


Finally:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub

HandleErr:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
        "Source: " & Err.Source & " " & vbCrLf & _
        "Number: " & Err.Number & " " & vbCrLf & _
        "Description: " & Err.Description & " " & vbCrLf

GoTo Finally
End Sub


I got help from the following sources:
http://www.mrexcel.com/forum/excel-questions/587522-copy-usedrange-except-header.html
http://stackoverflow.com/questions/59075/save-each-sheet-in-a-workbook-to-separate-csv-files
http://www.excelfox.com/forum/f2/export-all-worksheets-to-separate-csv-files-388/
http://windowssecrets.com/forums/showthread.php/140173-Open-excel-workbook-save-each-worksheet-as-csv-with-tab-name

Excel VBA copy sheet values only

posted Dec 28, 2013, 1:36 AM by Jaco Vosloo   [ updated Dec 28, 2013, 1:53 AM ]

Today I wanted to copy the values from every sheet in one workbook into a new workbook with only the values.  Here is what worked for me eventually:

For Each Sheet In Sheets
    Set NewWorkbook = Workbooks.Add
    Sheet.UsedRange.Copy
    NewWorkbook.Sheets.Item(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
...


The URLs where I found advice were in no order of preference:
http://www.mrexcel.com/forum/excel-questions/571065-used-range-copy-paste-values.html
http://www.ozgrid.com/forum/showthread.php?t=37957
http://www.ozgrid.com/forum/showthread.php?t=79718
http://stackoverflow.com/questions/14881032/copy-excel-sheet-to-another-excel-book-but-just-formats-and-values
http://stackoverflow.com/questions/17565456/macro-to-copy-values-only-from-one-sheet-to-another
http://www.mrexcel.com/forum/excel-questions/650606-copy-worksheet-values-only-new-workbook.html



Exchange gateway - DavMail

posted Nov 15, 2013, 5:47 AM by Jaco Vosloo

DavMail hides the proprietary Microsoft Exchange protocols behind an industry standard API so that any open source client can interact with Exchange. From their website:

DavMail is a POP/IMAP/SMTP/Caldav/Carddav/LDAP exchange gateway allowing users to use any mail/calendar client (e.g. Thunderbird with Lightning or Apple iCal) with an Exchange server, even from the internet or behind a firewall through Outlook Web Access. DavMail now includes an LDAP gateway to Exchange global address book and user personal contacts to allow recipient address completion in mail compose window and full calendar support with attendees free/busy display.

The main goal of DavMail is to provide standard compliant protocols in front of proprietary Exchange. This means LDAP for global address book, SMTP to send messages, IMAP to browse messages on the server in any folder, POP to retrieve inbox messages only, Caldav for calendar support and Carddav for personal contacts sync. Thus any standard compliant client can be used with Microsoft Exchange. DavMail gateway is implemented in java and should run on any platform.


Linux Shift Keypad arrow problem

posted Nov 11, 2013, 1:22 AM by Jaco Vosloo

Windows allows you to use shift and a keypad arrow to select words.  In Linux the shift keypad arrow toggles numlock and writing a number as described here by xenopeek.  Fortunately you can make the keypad and shift work the same as in windows as described here by mvaldez which is:

Go to the System Settings > Keyboard Layout > Layout > Options
Select Miscellaneous / Compatibility options > Shift with numeric keypad keys work as in MS Windows.

Virtualise dual boot windows on Linux

posted Nov 7, 2013, 9:10 AM by Jaco Vosloo   [ updated Nov 7, 2013, 9:41 AM ]

This is how to run a dual boot Windows 8 on Linux Mint / Ubuntu in Virtualbox.

Install virtualbox.
Create a virtual machine without a disk.
Go to the virtual machine folder.

sudo usermod -a -G disk jpv

install-mbr -e12 --force windows.mbr

VBoxManage internalcommands createrawvmdk -relative -filename windows.vmdk -rawdisk /dev/sda

In Virtualbox attach the new disk to the SATA storage setting.

Start the virtual machine.

Your normal boot menu will show, make sure you select Windows, it might not be a good idea to start the host OS as a guest in itself.

Windows will spend some time detecting new drivers, etc. and then boot up.

If Windows reboot too many times, try using the install DVD's repair function.

Note: If you don't want the normal boot menu to show, look at the MBR option of VBoxManage (-partitions 1,2 -mbr windows.mbr) I could not get it to work.

Linux mouse sensitivity is too high

posted Nov 7, 2013, 8:33 AM by Jaco Vosloo

Damn XWindows on Linux is unable to scale the mouse sensitivity.  Which means that a high sensitivity mouse cannot be used, unless you use the following script which was addapted from this post by Raphael Ahrens.

#!/bin/bash

if [ "$SEARCH" = "Logitech USB Receiver" ]; then
    exit 1
fi

ids=$(xinput --list | awk -v search="$SEARCH" \
    '$0 ~ search {match($0, /id=[0-9]+/);\
                  if (RSTART) \
                    print substr($0, RSTART+3, RLENGTH-3)\
                 }'\
     )

for i in $ids
do
    xinput set-prop $i 'Device Accel Profile' -1
    xinput set-prop $i 'Device Accel Constant Deceleration' 2.0
    xinput set-prop $i 'Device Accel Velocity Scaling' 1.0
done

IT testing as a profession

posted Oct 27, 2013, 4:56 PM by Jaco Vosloo   [ updated Oct 27, 2013, 5:01 PM ]

Once again I realise how slow business managers are in catching up with and grasping the opportunities and risks involved in what is often dismissively called I.T.  For example, the testing of IT systems (software, networks and hardware) has never really featured on the CEO's dashboards or at Business College.  Yet, it is probably one of the most powerful risk management functions within an organisation.  This was recently highlighted, courtesy of Knight Capital and the investigations following their small ($400 million) blunder.

Testing of IT systems is very similar to the auditing of financial books. It is the double entry method for technology systems, first enter the code into the computer and secondly enter the expected results into the tests.  No amount of business requirements, system designs, architectures or other documentation can make up for a lack in testing.  If your organisation is not prioritising testing over documentation then you are doomed to have high overheads, low returns and unmanageable risks.

It's amazing how low middle management's regard for their testing resources are and how little they expect of their testers.

  • Management should expect testers to be involved in the design of every system, the functional tests should be part of the business requirements (BDD).
  • The testers should be involved with the architects in defining the non-functional tests of the architecture.
  • The testers should be involved in the development phase by automating the functional tests and integrating with the unit tests provided by the developers.
  • Finally, the testing phase itself should not exist because it should already be done when development is complete.

I am inspired to see people like Aldo Rall go to great lengths to explain the benefits of proper testing to business and IT management. If you are a senior manager you should understanding how IT gets work done.  If you are an IT manager, you should consult with people like Aldo on how to improve your testing division.

Ubuntu 11.10 Wifi problems on Lenovo Edge 13

posted Nov 11, 2011, 5:16 AM by Jaco Vosloo   [ updated Nov 11, 2011, 11:12 PM ]

My Thinkpad's wireless just wouldn't work in ubuntu even though it worked in Windows, eventually putting some tape on the PCI express connector of the Wifi card did the trick.

Here are a few troubleshooting steps for wifi problems on a Lenovo Edge 13 laptop.
Run all these commands in a terminal.

1. Start here:

The following is what you want to see.  If there is a yes in any of the lines or if the Wireless item is missing then go to the relevant troubleshooting section:
# sudo rfkill list all
8: phy3: Wireless LAN
    Soft blocked: no
    Hard blocked: no
10: tpacpi_bluetooth_sw: Bluetooth
    Soft blocked: no
    Hard blocked: no
If you see the above then your hardware is turned on and ready to connect, now you must install and configure the "network-manager" or "WICD" which is not covered here.

2. No Wireless LAN listed

If the wireless lan is not listed then the hardware or your drivers may be missing:
Check if hardware is there:
# lshw -class net -short
H/W path               Device     Class       Description
=========================================================
/0/100/1c.1/0          wlan0      network     RTL8191SEvB Wireless LAN Controller
/0/100/1c.5/0          eth0       network     RTL8111/8168B PCI Express Gigabit Ethernet controller

Check if drivers are loaded:
Take a few number(not letters) from the above and see if the driver is loaded:
# lsmod |grep 819
rtl8192se              99931  0
thinkpad_acpi          81819  0
rtlwifi               110972  1 rtl8192se
mac80211              462092  2 rtl8192se,rtlwifi

If the driver is not loaded then you must find out how to compile it.

3. Soft blocked: yes

The radio is turned off by software, run:

# sudo rfkill unblock all

go back to 1 and re-test.

4. Hard blocked: yes

The Lenovo does not have a hardware radio switch.  It does however have a hotkey combination.
Press "Fn-F9" then return to 1 to re-test.
If the button does nothing then check if ACPI is installed:
# lsmod |grep think
thinkpad_acpi          81819  0
nvram                  14413  1 thinkpad_acpi
snd                    68266  19 thinkpad_acpi...

4.1 Check if the keyboard event is fired:

# xev | sed -n 's/^.*state \([0-9].*\), keycode *\([0-9]\+\) *\(.*\), .*$/keycode \2 = \3, state = \1/p'
keycode 246 = (keysym 0x1008ff95, XF86WLAN), state = 0x0

The above keycode says XF86WLAN which means the correct button has been pressed.

Find the event ID, look for the ThinkPad Extra Buttons event:
# lsinput
/dev/input/event10
   bustype : BUS_HOST
   vendor  : 0x17aa
   product : 0x5054
   version : 16641
   name    : "ThinkPad Extra Buttons"
   phys    : "thinkpad_acpi/input0"
   bits ev : EV_SYN EV_KEY EV_MSC EV_SW

Listen for the event ID. Put the above event ID in the command below :
# input-events 10
/dev/input/event10
   bustype : BUS_HOST
   vendor  : 0x17aa
   product : 0x5054
   version : 16641
   name    : "ThinkPad Extra Buttons"
   phys    : "thinkpad_acpi/input0"
   bits ev : EV_SYN EV_KEY EV_MSC EV_SW

waiting for events
08:52:13.257779: EV_MSC MSC_SCAN 4
08:52:13.257789: EV_KEY KEY_WLAN (0xee) pressed
08:52:13.257793: EV_SYN code=0 value=0
08:52:13.257809: EV_MSC MSC_SCAN 4
08:52:13.257813: EV_KEY KEY_WLAN (0xee) released
08:52:13.257816: EV_SYN code=0 value=0

4.2 Hotkey won't lift hardblock

If all else fails and the hotkey won't lift the hardblock then override the hardblock connection on the card.
If it is PCI express then put a small piece of tape over pin 20 of the connector as per this discussion.

References:

The hardware wireless button does not enable wifi as per the following bug report.
https://bugs.launchpad.net/ubuntu/+source/rfkill/+bug/786233

This person got it working somehow...
http://ubuntuforums.org/archive/index.php/t-1770202.html

http://en.gentoo-wiki.com/wiki/Lenovo_Thinkpad_T61#Wireless_switch_on_the_front_beneath_firewire

http://www.thinkwiki.org/wiki/How_to_get_special_keys_to_work

Disable the hardware switch:

On PCI-E Pin 20 is the hardware switch pin.  Insulate it with some tape and the hardware can not be switched off.
http://www.notebookforums.com/t/225429/broken-wireless-hardware-switch-fix
http://www.mp3car.com/attachments/wireless-communications/56148d1249724286-hacking-mini-pci-e-hsdpa-into-aopen-i45gmt-hd-board-sim_to_mini_pci_express.jpg



Phonegap with Barcode scanner

posted Oct 20, 2011, 2:33 PM by Jaco Vosloo

Here's a great tutorial on using Phonegap with the ZXing barcode scanner plugin.
 
The phonegap plugin does not work with Phonegap 1, so the following fixes is required:
Remove line107 from barcodescanner.js:
PluginManager.addService("BarcodeScanner","com.beetight.barcodescanner.BarcodeScanner");

 

1-10 of 26