Wednesday 2 September 2009

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