Monday, 26 May 2014

Random stuff we need for the VW T4 Transporter

I'm not expecting anyone to be interested in this, it's just a handy place to store the wish list....

Cover for sliding door rail and ceiling panel, saves faffing about with carpeting all the way along the top edge

Bench double seat swivel base

Short-shift mod

Towbar wiring and cluster diagram

Monday, 5 August 2013

VBA Modules: I've started a new blog

EDIT: left the background info up but have moved all my code posts into a new separate code blog

Original post and info follows:

As a bit of personal background, I've been developing in VBA automation since 2009, with about 3 years of moderate Excel & Access VBA development behind that, so the majority of the code is designed with Excel automation in mind, but does make use of Access and Outlook VBA where needed.  I've never had to automate Powerpoint or other MS apps but I'm sure I will eventually.

I work for a globocorp, and all the network addresses you will see here are completely inaccessible to anyone outside the company, so just change them for your own file locations.

Please note that I make no claims to copyright as all my code is open source, largely stolen, copied, or adapted from other snippets of code I've found via Google, usually with the original source link included for reference and polite attribution.  I've never had any formal VBA training, so this is all picked up from the interweb, and adapted for my own needs, using my own fair hands.  Feel free to steal, copy or adapt my code for your own needs -- that's what I did!

The modules I'll post here are interlinked (so you'd need to "install" the whole series to get full functionality and stop errors with Debug > Compile) but they do largely work as standalone modules.  I've annotated which modules are dependent on which, so it's easy to see that for yourself.

I've been loathe to post these modules online because they're never, ever "finished", as I keep adding new features as and when I need them.  But, in a stroke of momentary genius a while ago, I started version-numbering the macros to keep track of the various changes, and I've recently started keeping a Version History in all modules, so I reckon I can now keep my blog up-to-date with changes.  (We'll soon see!)  Plus, after migrating everything to a new server with Office 2010, I think I'm finally running out of necessary improvements to make.  It does just about everything I need it to.

However, I must be clear about this: this code is not for the faint-hearted.  I don't always put syntax or other descriptive details in the macros, so you'll need a fair bit of VBA knowledge under your belt to make sense of some of it.  But it should at least work... feel free to just try it out, and ask me questions if you're not sure about how anything works or what it does -- I'll improve the code annotations as I go along -- but if you're a VBA n00b, there are countless Excel / VBA forums with hundreds of members where you can get basic support, just as I did.  VBA has a pretty steep learning curve if you don't have the time to develop your skills gradually.  I had 5 years to develop mine.  Another guy I saw asking a complete n00b question on a forum ended up with his profile being marked as "Excel VBA MVP" within 3 years.  So before you jump from the high dive board, I'd strongly suggest you either pay for a professional VBA course, hire a spod to do it, or just think again and start in the shallow end.

First module here:

Friday, 4 January 2013

VBA Macro to remove special characters from path and/or filename and/or VBA object name

Solution originally posted here: 

This VBA function removes special characters from a string.  In my case I wanted a single function to return a valid path and/or filename and/or VBAProject name.  It works with both URL and UNC paths (and tries to clean up any paths with mixed slashes).

It's easy enough to tweak this for other purposes.  You can specify additional "forbidden" characters easily and add any extra boolean switches for your own specific needs, or you could just split into separate functions.

The function also checks the maximum string length and either crops or pops up a message box if a filename (not path) exceeds 128 characters -- very useful for SharePoint uploads -- or a VBA object name exceeds 35 characters.

Function fn_Clean_Special(str As String, CropLength As Boolean _
    , Optional VBObjectName As Boolean) As String
'v1.03 2013-01-04 15:54
'removes invalid special characters from path/file string
', True stops message box warnings and autocrops string
'     [, True] also removes spaces and hyphens and periods (VBA object)
'~ " # % & * : < > ? { | } ..   / \   -

Dim b As Integer, c As Integer, pp As String
Const tt As String = "fn_Clean_Special"
Dim sc(0 To 18) As String
sc(0) = "~"
sc(1) = Chr(34)  ' Chr(34) = " quotemark
sc(2) = "#"
sc(3) = "%"
sc(4) = "&"
sc(5) = "*"
sc(6) = ":"
sc(7) = "<"
sc(8) = ">"
sc(9) = "?"
sc(10) = "{"
sc(11) = "|"
sc(12) = "}"
sc(13) = ".."
'slashes for filenames and VB Object names
sc(14) = "/"
sc(15) = "\"
'hyphen & space & period for VB Object names
sc(16) = "-"
sc(17) = " "
sc(18) = "."

'remove special characters from all
For b = 0 To 13
    str = Replace(str, sc(b), vbNullString)
Next b

'check filename length (length AFTER the LAST slash max 128 chars)
b = InStr(1, str, sc(14))  'look for fwd slash
If b > 0 Then
    str = Replace(str, sc(15), sc(14))  'remove all back slashes
    Do Until b = 0  'until last slash found
        c = b       'c is position of last slash
        b = b + 1                   'next position
        b = InStr(b, str, sc(14))   'next position
Else  'no fwd slashes
    b = InStr(1, str, sc(15))  'look for back slash
    If b > 0 Then
        str = Replace(str, sc(14), sc(15))  'remove all fwd slashes
        Do Until b = 0  'until last slash found
            c = b       'c is position of last slash
            b = b + 1                   'next position
            b = InStr(b, str, sc(15))   'next position
    End If
End If
'c is position of last slash, or 0 if no slashes
If Len(str) - c > 128 Then
    If CropLength = True Then
        str = Left(str, 35)
        pp = "WARNING: filename > 128 chars"
        MsgBox pp, vbCritical, tt
    End If
End If

'remove slashes from filenames only
If c > 0 Then
    For b = 14 To 15
        str = Left(str, c) & Replace(Right(str, Len(str) - c), sc(b), vbNullString)
    Next b
End If

If VBObjectName = True Then
'remove slashes and swap hyphens & spaces & periods for underscore in VB object name
    Const scUS As String = "_"
    For b = 14 To 18
        str = Replace(str, sc(b), scUS)
    Next b
'then remove invalid characters from start of string
    Dim c1 As String
    c1 = Left(str, 1)
    Do While c1 = scUS Or c1 = sc(18) Or IsNumeric(c1)
        str = Right(str, Len(str) - 1)
        c1 = Left(str, 1)
'remove double underscore
    Do While InStr(str, scUS & scUS) > 0
        str = Replace(str, scUS & scUS, scUS)
    'check object name length (max 35 chars)
    If Len(str) > 35 Then
        If CropLength = True Then
            str = Left(str, 35)
            pp = "WARNING: object name > 35 chars"
            MsgBox pp, vbCritical, tt
        End If
    End If
End If

fn_Clean_Special = str

End Function

Debug Window results:

?fn_clean_special("\\server\path\filename.xls", True)
?fn_clean_special("\\server\path\filename.xls", True, True)

?fn_Clean_Special("\\special character\testing   for \VBproject.xls", True, True)

Friday, 19 October 2012

Grom Audio USB2 - Excel macro to Rename and Sort Tracks

Excel .xlsm file with a VBA macro that will sweep through a list of folders and rename all the tracks to a standard format.  It renames "Trackname 001.mp3" to "001-Trackname.mp3" and "Album - 01 - Trackname.mp3" to "01-Album-Trackname.mp3".  It skips files without track numbers so it won't affect your playlist files.

If required, I can add function to recurse through all subfolders, just contact me for more info.  Likewise if you need this file in Excel 2003 format.

Website structure was lost a while back, file now moved here:

Wednesday, 8 February 2012

Volvo S40/V40 Guide: Removal of indicator/wiper stalks

Tools required: Philips screwdriver (PH2)

Cowling = Steering column housing, plastic trim, one large piece below the column, one smaller piece atop.

Four broad, short black screws with black washers (i.e. standard trim screws) affix the trim piece above the footwell (i.e. the piece with the footwell light) - unscrew all four then this trim slides right and towards you and comes away easily

Two short black screws with black washers affix the bottom piece of cowling to the steering column, just underneath the steering wheel, facing you. Unscrew these.

One very long black screw (upper right of the 3 holes under the central cowling) simply fixes the upper+lower cowling pieces together, unscrew this, the top piece should then prise off with minimal force (note the couple of plastic tabs to clip the cowling pieces together again)

Release the steering column lever and pull the wheel down, the bottom piece then pulls away easily to reveal the innards of the column and both indicator stalks

Two small black philips screws hold each stalk into position, unscrew both and pull the stalk away then unclip the cabling plug.

You can then dismantle the stalk itself with another two small black philips screws, and give it a clean inside if it's full'o'muck.

I suspect that one of the two switches (right hand side) is either gunged up or faulty, but I'll clean off the gunge first.

Muck I wiped off (mostly grease to be honest) shown in photo below, but this didn't fix my problem. I suspect one of the two switches is faulty. Need to test the wiring and contacts with a voltmeter next.

Whole process takes about 2-3 minutes.

Putting it all back together again is a bit more of a fiddle, but pay attention to the two cowling pieces which should clip together neatly, and the rubber seals around the stalks sit flush outside the cowling, so getting both in snugly is a bit of a faff, but reassembly took me 5 minutes in total. Screwing the bottom piece back to the column before clipping the two pieces together worked best for me, but ymmv.

NB: I think the shiny screws are holding the ignition barrel in place - it seems I'd have to remove the steering wheel to get access to them anyway.

Wednesday, 28 December 2011

Problems with taking ownership / access rights in Windows 7

If you ever suffered the major and incredibly frustrating problems with Windows 7 when moving an NTFS hard disk to a new PC, or reinstalling the OS: the inability to open, delete, take ownership, or amend permissions on files or folders, then look no further than this thread on Microsoft social support forums

Note my post towards the end of that thread, made today, which amends the excellent original instructions provided by ColtWanger (lol) as follows:

I have thousands of files across hundreds of folders that Win7 simply won't allow me to access since I moved the drive from another machine. I have SP1 installed and although I haven't wasted any more time messing with permissions and audit rights I'm still getting the same issues with being denied access when I try to open the files. So I followed Coltwanger's advice above and amended slightly for my needs:

click Start and type CMD
right-click Command Prompt to run as Administrator

in the cmd window, use cd to find the relevant folder or drive root. In my case this required two commands, D: then cd\

First run TAKEOWN to take ownership.

TAKEOWN /F *.* /A /R /D Y
/F *.* processes all files/folders
/R recurs for all subfolders and files
/A takes permission for the Administrators group (I could easily have specified my own username, or omitted this switch, it shouldn't be necessary as I'm running CMD as Administrator anyway but I was wary that the next step might have failed)
/D Y suppresses the confirmation prompts

Then run ICACLS -- I amended with /grant:R to replace all existing permissions. This fixes a bug where unrecognised owners can remain in place. You can manually add extra user permissions via Windows once this is done.

ICACLS D:\* /grant:R baldmosher:F /T

Successfully processed 31380 files, 0 failures. YAAAAAY!!!!

Update 2012-02-08:

I made copies of all the above folders onto a different hard drive, once I got admin access to them, just to make sure that the permissions from my new HomeGroup setup are the default on all the folders. Much easier than trying to second guess Windows and set them yourself using command line options.

Update 2012-08-05: 

Today I realised my wife couldn't access the shares from her laptop.  I thought about adding her to every shared folder, but I figured it was easier to do it properly, and give access to the HomeGroup.

I also realised she didn't have a user ID on this machine, so I had to do that first (making sure the username and password matched the one on her laptop), then the following at command line run as Administrator:

ICACLS D:\* /grant:R HomeUsers:F /T

Friday, 18 November 2011