Sorting out Excel Comments

The following Macro reformats the comments in  a spreadsheet when the Excel bug has messed them all up …

Sub AutosizeComments()
 ' Alt F11 + Insert - Module
 ' Insert this macro then close the VBA
 ' then alt F8 to run and select the AutoSizeComments option
 ' moves the comments to rght by where you need them, adjust width and Heght as you see fit
 Dim cmt As Comment, cell As Range
 On Error Resume Next
 For Each cell In ActiveSheet.UsedRange
 Set cmt = cell.Comment
 If Not cmt Is Nothing Then
 cmt.Shape.Placement = xlMove
 cmt.Shape.Left = cell.Left + cell.Width + 30
 cmt.Shape.Top = cell.Top + 10
 cmt.Shape.Width = 200
 cmt.Shape.Height = 150
 cmt.Shape.Left = c.Offset(0, 1).Left
 cmt.Shape.Top = c.Offset(-1, 1).Top
 End If
 Next cell
 End Sub
Posted in Uncategorized | Leave a comment

Connecting a Raspberry Pi Zero over USB

Some notes on how to use a Pi Zero without a lot of adapters and cables.

The Pi Zero is a cool bit of kit which has amazing capabilities but tends to need a spaghetti of wires to get it connected and up and running.

I read on the internet that the device can be used in a USB on the Go mode and in order to have a device that I can use pretty much anywhere I thought I’d give it a go.

I followed the instructions on the page which are pretty self explanatory, although there were a couple of areas that weren’t entirely clear to me that I think I will clarify…

To make life easy, I used a Raspberry Pi 2 which I have to prepare the card ready for the Pi Zero. I downloaded the latest Raspian Jessie full implementation from the Raspberry Pi Foundation download page, expanded it from the zip file under Windows 10 and then wrote it to a Micro SD card using Win32Imager.

Once the writing was complete, I loaded the card into my Pi 2 with the easy to access ports and network to allow me easy access to the installed version to prepare it.  All the steps that you need to do can be done on this device, other than the update of the Pi firmware. (On my Pi Zero it was at the latest firmware level already so although that is in the instructions, if you got your Pi Zero in 2016 I suspect that you will be up to date enough for it to work)

The steps I used to get the Pi prepared were as follows

  • boot with keyboard, mouse and screen
  • set the Pi to boot to the command prompt as shown here and reboot
  • resize the root partition (sudo raspi-config, select expand and reboot)
  • add the line dtoverlay=dwc2 to /boot/config.txt (used sudo vi)
  • add the lines dwc2 and g_ether to /etc/modules (used sudo vi)
  • shutdown
  • the card is now mostly prepared and can be put into the Pi zero
  • I connected a power cable to the power socket and a network cable to a USB ethernet adaptor
  • I checked my router to find the IP address of the Pi Zero
  • Use ssh to log on to the Pi and then run the firmware update  sudo BRANCH=next rpi-update
  • Finally I added the config to the /etc/dhcpcd.conf file (sudo vi)
    interface usb0
    static ip_address=
  • shutdown

This was all the prep and all that remained was to test the Pi

I plugged a standard USB cable into my PC and the mini end into the Pi, not into the Power port, but into the USB port. The PC is quite capable of providing the power for the Pi zero, so it becomes a single wire connection

It took a while for the drivers to be downloaded and installed for the USB ethernet

When it is recognised it appears as an adapter in the Control Panel ¬ Network and Internet ¬ Network Connections

You should be able to ping from your PC and then ssh to to access the Pi.

Remember this doesn’t give you access to the Internet from the Pi, but it does let you use it as a webserver or SQL database or programming test environment if you chose from any other computer, once config has been done.


Posted in Uncategorized | Leave a comment

Auto mounting drives at boot under OSX

I keep losing this nugget of information and after having just re IP addressed my network, this is now needed

OSX runs stuff using /etc/auto_master

Mine looks like this

hopenet-mac-mini:etc tim$ cat auto_master
# Automounter master map
+auto_master # Use directory service
/net -hosts -nobrowse,hidefromfinder,nosuid
/home auto_home -nobrowse,hidefromfinder
/Network/Servers -fstab
/- -static
/- auto_smb
hopenet-mac-mini:etc tim$

The last line points to a file


hopenet-mac-mini:etc tim$ cat auto_smb
/mount/media_server -fstype=smbfs ://guest:guest@
/mount/movies -fstype=smbfs ://guest:guest@
/mount/music -fstype=smbfs ://guest:guest@
/mount/mp3 -fstype=smbfs ://guest:guest@
/mount/pictures -fstype=smbfs ://guest:guest@
hopenet-mac-mini:etc tim$

These links provide guest access to the server and persist…

Posted in Uncategorized | Leave a comment

Coding fonts

Posted in Uncategorized | Leave a comment

Excel spreadsheets without Excel

This looks interesting, a way of writing Excel spreadsheets without Excel, inclusing some of th emore advanced functions like charts and PowerPivots

Posted in Uncategorized | Leave a comment

Hacking Powershell to Unrestricted

Here’s how to get ExecutionPolicy Unrestricted, the registry way …

Use Get-ExecutionPolicy to check it

  • Open ‘run’, type ‘regedit’ and hit enter
  • Using the navigation tree drill down this path:
  • If it does not exist then create a new key by right-clicking–>new key:
      Type: REG_SZ
      Name: “ExecutionPolicy”
      value: “RemoteSigned”
Posted in Uncategorized | Leave a comment

Free DNS

Now DynDNS has gone, we need an alternative

How about

Free and simple with clear instructions on how to background script updates

Posted in Uncategorized | Leave a comment