How to install Webmin on Trixbox

Webmin is a valuable tool used for the configuration of Linux-based servers. Webmin installs an HTTP-based GUI which you can get to by using port 10000 from a browser.

To install Webmin, you need to run the following commands in your Linux CLI:

wget –no-check-certificate https://sourceforge.net/projects/webadmin/files/webmin/1.881/webmin-1.881-1.noarch.rpm
rpm -i webmin-1.881-1.noarch.rpm

Once the install has finished, you can get to your Webmin console by putting the following into a browser that exists on your LAN:

https//ServerIPAddress:10000/

(replace ServerIPAddress with the IP address of your Trixbox)

Use the WiiMote as a mouse in Linux

In my search for using the WiiMote as a musical instrument I found out it’s rather easy to setup your system to use the WiiMote as a mouse. Some of the steps needed to configure your system are actually the same, for being complete I’ll document all steps in this article.

 

Prerequisites (what you need to get it working):

  • Hardware
    • Computer able to run Ubuntu (I prefer Ubuntu Studio).
    • Bluetooth adapter (an adapter for using an open wireless protocol for exchanging data over short distances).
    • Wii Remote (also known as a wiimote, the primary controller for Nintendo’s Wii console)
  • Software
    • Ubuntu Studio (a multimedia editing/creation flavor of Ubuntu. It’s built for the GNU/Linux audio, video, and graphic enthusiast or professional)
    • CWiid(a collection of Linux tools written in C for interfacing to the Nintendo Wiimote)

 

Allright let’s get started :

Where possible I’ll provide the links to the necessary download locations.

You either have an pre installed bluetooth adapter in your computer or you can buy one in the shop (make sure it’s linux compatible).

As we’re using Ubuntu Studio, you can download the DVD Image and burn it to DVD.

You should think over what you going to do with your configuration, repartition your hard disk (or don’t), and install Ubuntu Studio

Make sure you’ve got a Wii Remote with enough power.

 

Open a terminal session in Ubuntu then copy and paste the following instructions:

sudo apt-get install libcwiid1 lswm wmgui wminput

 

Sometimes it comes in handy to know your gear so at this point, you can turn on the Wii remote to scan by pressing 1 and 2 simultaneously (all the lights will flash) then running:

hcitool scan

 

After you installed the necessary packages, you we’ll be able to give it the first shot by entering wmgui in a terminal window

wmgui

 

Select “connect” from the file menu, press 1+2 on the Wiimote when prompted then click OK. Lights and rumble can be turned on and off from the controls menu, and which inputs are displayed from the settings menu. Using this, you can test the IR camera (I didn’t have infrared lights so I used a candle (BE VERY CAREFULL WITH OPEN FIRE IN AND AROUND YOUR LIVING AREA not to set the place on fire)), the accelerometers and check the inputs from the Nunchuck or Classic Controller.

 

Now you know basic set-up is working (your computer running Ubuntu Studio, your bluetooth adapter, your Wii Remote and the “connection” between it all).

 

From here on things are different, from the article about using the WiiMote as a musical instrument (check here).

 

For using the WiiMote as a mouse we need a mouse emulator (a small program that converts WiiMote output to mouse output) the one we are going to use is called uinput.

Before being able to use uinput we need to load it into the kernel, this can be done in two ways: manually after every reboot or we can load it every time the system starts up.

 

Manually

Copy and paste the following instructions:

sudo modprobe uinput

 

Loading it up every time the system starts up by adding uinput into /etc/modules:

gksudo gedit /etc/modules

 

Edit and save /etc/modules (mine looks like this)

 

Now reboot your system if you choose the latter option (editing /etc/modules).

 

We need the MAC-address of our WiiMote, there are multiple ways of getting this address:

hcitool scan
lswm

Now we can start doing our mouse thing by telling wminput to listen to the right WiiMote (telling the WiiMote to listen to which MAC address) (make sure you use your own MAC-address !!!):

sudo wminput 00:24:F3:E3:E6:CD

 

(if you get the following error “unable to open uinput” try using the wminput command in SU mode as you didn’t have enough rights to use uinput).

 

Now you’re ready to rock and roll!!

 

Two more things:

  • You can close the terminal window if you want to
  • If you’re ready using your WiiMote press the off button on your Wiimote.

 

Suggestions for improving this article are welcome, please let me know and drop me a line!

 

How to split numbers and text from a string in Excel

I received an e-mail from Marc, in which he asked me whether it is posible to split numbers and text in Excel. The answer of the solution is based upon determining the first position of a number in a string, you can do that by using the following formula.

=MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))

 

The next trick is to determine the text within a string:
A101 will become A when using the following formula

=LEFT(A1;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))-1))

101A will become A when using the following formula

=RIGHT(A2;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

 

To determine the numbers within a string:
A101 will become 101 when using the following formula

=RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1)

101A will become 101 when using the following formula

=LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))

 

 

You might want to convert the text string to numbers by using the following formula.

=VALUE()

A101 will become numeric value 101 when using the following formula

=VALUE(RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1))

101A will become numeric value 101 when using the following formula

=VALUE(LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

Rearrange columns in Excel using VBA

Did you ever need to rearrange or reorganize columns across multiple sheets in a certain order based on column headers? In this article I’ll try to explain how to rearrange columns in Excel based on column header information by using Visual Basic for Applications (VBA) .

As mentioned in the intro, this article is about rearranging columns in Excel using column header information.

To make it more visible see the images below…

In this example the headers are in alpabetical order:
Address, City, Country, Date of Birth, First Name, Last Name, Middle Name, Phone Number, Postal (ZIP) Code, State.

 

And let’s say you want to change the column order to:
First Name,Last Name, Middle Name, Date of Birth, Phone Number,Address, City, State, Postal (ZIP) Code, Country (see the image below)

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • First make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • You might want to use your own headers and ordering, so change the code there 😉
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

 

Sub MoveColumns()
‘ MoveColumns Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 03-10-2011
‘ Modified: 03-10-2011
‘ Version: 1.0

‘ Description: Rearrange columns in Excel based on column headerDim iRow As Long
Dim iCol As Long’Constant values
data_sheet1 = InputBox(“Specify the name of the Sheet that needs to be reorganised:”) ‘Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = “Final Report” ‘Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count ‘Determine how many rows are in use’Create a new sheet to store the results
Worksheets.Add.Name = “Final Report”

‘Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count

‘Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0

‘Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = “First Name” Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = “Middle Name” Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = “Last Name” Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = “Date of Birth” Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = “Phone Number” Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = “Address” Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = “City” Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = “State” Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = “Postal (ZIP) Code” Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = “Country” Then TargetCol = 10

‘If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
‘Select the column and copy it
Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If

Next iCol ‘Move to the next column until all columns are read

End Sub

 

Additional information 1

Someone sent me an alternative solution for reorganizing columns in Excel. The script makes use of the array function in Excel. It does a really nice job but beware, the  code handles your data in a way that it does keep your original data structure.

Sub Reorganize_columns()
‘ Reorganize Columns Macro

‘ Developer: If you want to know, please contact Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 11-11-2013
‘ Modified: 11-11-2013
‘ Version: 1.0

‘ Description: Reorganize columns in Excel based on column headerDim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim iNum As Long
v = Array(“First Name”, “Middle Name”, “Last Name”, “Date of Birth”, “Phone Number”, “Address”, “City”, “State”, “Postal (ZIP) Code”, “Country”)
For x = LBound(v) To UBound(v)
findfield = v(x)
iNum = iNum + 1
Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
End Sub

 

Additional information 2

Dennis Klenner from D&R Design Ltd wanted you to know “Header names are case sensitive”! Thank you for the remark Dennis 🙂

 

Suggestions for improving this article are welcome, please let me know and drop me a line .

 

 

Convert MAC address in MS Excel or OO Spreadsheet

As I’m currently working a lot with Excel, I get a lot of questions like “How do I do this?” or “How do I do that?”. Yesterday someone asked me how to convert a data string to a MAC address. The standard format for printing MAC addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:), in transmission order, e.g. 01-23-45-67-89-ab, 01:23:45:67:89:ab. In this article I’ll give you examples of how to add, remove or change the separator for MAC addresses in MS Excel or OO Spreadsheet.

 

Adding a seperator (change 0123456789ab into 01-23-45-67-89-ab)

=CONCATENATE(MID(A2;1;2);”-“;MID(A2;3;2);”-“;MID(A2;5;2);”-“;MID(A2;7;2);”-“;MID(A2;9;2);”-“;MID(A2;11;2))
=CONCATENATE(MID(A2;1;2);”:”;MID(A2;3;2);”:”;MID(A2;5;2);”:”;MID(A2;7;2);”:”;MID(A2;9;2);”:”;MID(A2;11;2))

 

An alternate solution was send in by Franco Magliaro see below:

=MID(A2,1,2)&”-“&MID(A2,3,2)&”-“&MID(A2,5,2)&”-“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2)
=MID(A2,1,2)&”:”&MID(A2,3,2)&”:”&MID(A2,5,2)&”:”&MID(A2,7,2)&”:”&MID(A2,9,2)&”:”&MID(A2,11,2)

 

Removing a separator (change 01-23-45-67-89-ab into 0123456789ab)

=CONCATENATE(MID(A2;1;2);MID(A2;4;2);MID(A2;7;2);MID(A2;10;2);MID(A2;13; 2);MID(A2;16;2))

or the easy way

=SUBSTITUTE(A2;”-“;””)
=SUBSTITUTE(A2;”:”;””)

 

Changing a separator (change 01-23-45-67-89-ab into 01:23:45:67:89:ab)

=CONCATENATE(MID(A2;1;2);”:”;MID(A2;4;2);”:”;MID(A2;7;2);”:”;MID(A2;10;2 );”:”;MID(A2;13;2);”:”;MID(A2;16;2))
=CONCATENATE(MID(A3;1;2);”-“;MID(A3;4;2);”-“;MID(A3;7;2);”-“;MID(A3;10;2 );”-“;MID(A3;13;2);”-“;MID(A3;16;2))

or the easy way 🙂

=SUBSTITUTE(A2;”-“;”:”)
=SUBSTITUTE(A2;”:”;”-“)

 

An alternate solution, to convert the Cisco 0123.4567.89ab MAC format to the F5 load balancer MAC format of 01:23:45:67:89:ab, was send in by Mike Wintrode. To add an extra twist, the F5 load balancer also removes the leading “0” in any pair, so for instance 00:14:0F:80:a3:01 would show as 0:14:f:80:a3:1.

So Mike created the following:

=CONCATENATE(IF(MID($A2,1,1)=”0″,MID($A2,2,1),MID($A2,1,2)),”:”,IF(MID($A2,3,1)=”0″,MID($A2,4,1),MID($A2,3,2)),”:”,IF(MID($A2,6,1)=”0″,MID($A2,7,1),MID($A2,6,2)),”:”,IF(MID($A2,8,1)=”0″,MID($A2,9,1),MID($A2,8,2)),”:”,IF(MID($A2,11,1)=”0″,MID($A2,12,1),MID($A2,11,2)),”:”,IF(MID($A2,13,1)=”0″,MID($A2,14,1),MID($A2,13,2)))

The basic premise utilizes an “IF” statement to check for a preceding “0” and if present to return the next character, and if not to return both characters.

 

Adam Heath was so kind to send me an Exel template which uses most of the above formulas
(thanks Adam!), which you can download here.

 

Suggestions for improving this article are welcome, please let me know and drop me a line.