Excel Setup TextToColumns default

I regularly use Excel's Text to Columns Wizard to convert Pipe "|" separated variable files to excel tables. The format of these files is very similar to comma-separated files (.csv) but uses a pipe character | instead of a comma.
Each time I've done this in the past I've had to manually copy the data in and then go through the wizard, changing the settings from the defaults to Other: | each time. I finally decided to try and change the default behaviour of the T2C wizard to automatically select Pipes instead of Tab.

How do I go about it?

The behaviour of the T2C wizard naturally remembers the last used settings, so once you've manually changed it, it remembers your choices for the rest of the session. The way I have implemented this is to add a workbook into Excel's XLSTART folder, containing a little macro which runs the T2C on a cell and then closes.
This file opens each time Excel starts, changes the t2c setting and closes itself, leaving you with a new empty book.

How To:
Create a new Excel file, press alt-F11 to open the VBA editor.
Double click on the "ThisWorkbook" item in the Project window
Copy and paste the code below into the text area
Close the VBA editor
Type some text into range A1 (anything you like)
Save the file into the following folder, with a useful name:
C:\Windows\Profiles\username\Application Data\Microsoft\Excel\XLStart

'This method lives in the ThisWorkbook code, and will automatically run when the 'workbook is opened.
Private Sub Workbook_Open()
'Turn off screen updating
Application.ScreenUpdating = False
'if the value in range A1 is empty, add a space so that the t2c doesn't fail
If IsEmpty(Range("A1")) Then Range("A1").Value = " "

'run the t2c on range a1 (of this template workbook)
'set the default options, in this case use OtherChar="|"
Range("A1").TextToColumns ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|"

'Create a new workbook (as book1 is not automatically created any more)
Workbooks.Add

'Turn screen updating back on
Application.ScreenUpdating = True

'Close the template workbook
ThisWorkbook.Close SaveChanges:=False
End Sub



Note that once you have saved the file each time you open it and enable the macro's it will automatically close itself, so if you want to edit it, you'll have to disable macros when prompted to do so.

Hope this helps,
Matt

Windows server 2003 logon and shut down annoyances fixed!

Something that I have found very annoying with Windows Server 2003 is that it asks you for a reason why you want to shut down, and also that it won't let you shut down the machine before logging in. I can understand why in large business environments this behaviour would be useful but for the way we use it at work it's just a PITA.
Finally remembered to try and resolve it this morning and quickly came across a great post from
Thomas Freudenberg's blog here. Which kills both birds with one stone.

tasklist and svchost.exe stuff

Came across something that I've often wondered about today.
Why do you always have lots of svchost.exe processes running when you look at task manager?
Well I've just worked out how to find out what they are, which could be very useful in the future if I have to try and fix someones spam-ridden pc again.

The tasklist command prompt tool lists all the running processes on your pc, in a similar fashion to Task Manager, however the strength comes in the parameters you can give it.

typing:
tasklist /SVC

shows the list of processes, with the names of the actual process not just the filename, very useful for identifying unusual processes, it will also tell you what svchost is really running. Useful.

Updating an SQL Table using a join

Today I needed to update one of the tables in my database replacing the value in one field with a derived value from another table. A quick search on the internet turned up this useful post from Ben Nadel


Essentially the statment required is as follows:

UPDATE A
SET DestinationField = B.SourceField
FROM TableToUpdate A
INNER JOIN TableToReadFrom B
ON A.JoinKey = B.JoinKey

How to stop infuriating windows system beeps?

from the command line, type:
net stop beep

and what if for some strange reason you actually want to turn them on again? You guessed it..
net start beep