Archive for March, 2009


VMWare Capacity Planner Upload Workaround


We’re currently working with an organisation to assess some virtualisation options on our network.  In order to gather some information on our existing estate we’re running the VMWare Capacity Planner.  This is an ‘agentless’ piece of software that monitors server utilisation and uploads data to a central repository on the web.

We have a couple of instances of this software running on our network, but there is (currently) an inexplicable issue in that one of the machines doesn’t appear to be able to get Internet access.  We’ve looked at the network configuration, firewalls, etc. but we’ve obviously missed something somewhere.

In the interim as a workaround we’re copying the data files from one machine to the other.  These data files are then periodically uploaded along with that machines files to the repository.  We’ve had confirmation from our consultant that this appears to be working so we’re now just looking forward to the final report.

The data files are held (by default) in C:\Program Files\VMware\VMware Capacity Planner\Outbox“, so simply copy the files between these folders on machines to get the data uploaded.


VBS – Duplicate Folder Structure


Armed with my folder browser script I finally finished rounding out my script to clone / duplicate a folder structure.

On occasion I produce a folder/directory structure which I’d like to be able to reuse as a template – e.g. on a project.  The problem is that it already has lots of files in and I have to copy the whole thing and then use an ‘open’ search to list the files in the new structure so that they can be deleted, but if there are lots of big files this operation can be slow and the inefficiency of it grates against my programmer’s nature.

Thus came about the writing of a little bit of VBScript to copy an existing folder structure and effectively clone it to another area on a file system.  So here’s the script … I hope you find it useful.

Option Explicit

Const ForReading = 1
Const ForWriting = 2
Const MAKE_FILE = "BuildFolders.txt"

Dim objFSO, objFolder, objFile
Dim strSourceFolder, strDestinationFolder, strBuildFoldersFile
Dim intFolders

'Initialise and capture folder paths
intFolders = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
strSourceFolder = SelectFolder("Select a source folder")
strDestinationFolder = SelectFolder("Select a destination folder")
strBuildFoldersFile = strDestinationFolder & "\" & MAKE_FILE

'Read, store and then build the new folder struture

MsgBox "Created " & intFolders & " folders", vbOKOnly & vbInformation, "Folder Generation complete"


'Read the source folder structure and store it
Sub CreateBuildFile()
	Set objFile = objFSO.CreateTextFile(strBuildFoldersFile, True)
End Sub

'Create the destination folder - should exist from the selection ... but just in case
'I (or someone else) wants to parameterise this script later on...
Sub CreatestrDestinationFolder()
	If Not objFSO.FolderExists(strDestinationFolder) Then
		intFolders = intFolders +1
	End If
End Sub

'Create the new folder structure
Sub MakeFromBuildFile()
	Set objFile = objFSO.OpenTextFile(strBuildFoldersFile, ForReading)
	Do While Not objFile.AtEndOfStream
		objFSO.CreateFolder(strDestinationFolder & objFile.ReadLine)
		intFolders = intFolders +1
End Sub

'Remove the file that was holiding the data structure
Sub DeleteBuildFile()
End Sub

'Write a folder structure to the build file
Sub ReadstrSourceFolders(p_strSource)
	Dim colSubFolders
	Dim objSubFolder

	Set objFolder = objFSO.GetFolder(p_strSource)
	Set colSubfolders = objFolder.Subfolders
	For Each objSubfolder in colSubfolders
End Sub

'Remove the source folder path from a string (i.e. a sub folder's path)
Function StripstrSourceFolder(p_strFolder)
	StripstrSourceFolder = Right(p_strFolder,(Len(p_strFolder)-Len(strSourceFolder)))
End Function

'Select a folder
Function SelectFolder(pstrDialogLabel)
	'Select a folder
	Const BIF_returnonlyfsdirs   = &H0001
	Const BIF_editbox            = &H0010

	Dim objBrowseFolderDialog, objFolder, objFSO, objSelection
	Dim bBrowseForFolder

	Set objBrowseFolderDialog = WScript.CreateObject("Shell.Application")

	bBrowseForFolder = true

	While bBrowseForFolder
		Set objFolder = objBrowseFolderDialog.BrowseForFolder (&H0, pstrDialogLabel, BIF_editbox + BIF_returnonlyfsdirs)

		'Check that something has been returned
		If IsValidFolder(objFolder) Then
			Set objFSO = CreateObject("Scripting.FileSystemObject")

			Set objSelection = objFolder.Self
			If objFSO.FolderExists(objSelection.Path) Then
				'A valid folder has been selected
				SelectFolder = objSelection.Path
				bBrowseForFolder = false
				'The selection is not a valid folder, try again...
				MsgBox objFolder.Title & " is not a valid folder, please select another folder" _
					, vbOKOnly & vbExclamation, "Invalid Selection"
			End If
			'Nothing was selected, so return a null string
			SelectFolder = ""
			bBrowseForFolder = false
		End If
End Function

Function IsValidFolder(pobjFolder)
	'Check that we have a valid value
	'i.e. you can concatenate it to a string
	Dim strTest

	On Error Resume Next

	strTest = " " & pobjFolder

	If Err  0 Then
		IsValidFolder = false
		IsValidFolder = true
	End If

	On Error GoTo 0
End Function

VBScript – Select a Folder


This post has now been migrated to

Follow this link to go directly to the article.


Merging URLs into SharePoint Document Libraries


After spending a bit of time looking at service desk records today I had the opportunity to look at a particular request where a SharePoint user wanted to include links to files on other web sites in a document library without uploading the file onto the SharePoint site.  This is actually a really nice idea as then if the other site is updated (assuming the URL remains the same), the latest version is always accessible via the SharePoint site.

Now normally links would be handled by using a link based web part, but the integration with a document library was what made this notably interesting.

Whilst a custom list web part could provide an interesting option, the document library the user wished to add to was already in use so I took the simpler and more pragmatic approach.

Most web browsers (notably Microsoft Internet Explorer which is the browser of choice for using Microsoft SharePoint) allow bookmarks to be saved out (e.g. by drag and drop from the address bar/field) into a simple URL file a.k.a. an Internet short cut file (at least you can in Windows XP which the user is running).  So if this can be done it would make sense to upload this into the document library.

Unfortunately by default URL files are on the ban list for SharePoint so a system administrator must first amend the list in the operations settings to allow URL files to be uploaded.

This should have been the solution, but when trying to upload the file I at first got a sample file being upladed onto SharePoint from another web site.  Exactly what I was trying to avoid.  However the option to upload multiple files uses a different upload process.

Choosing just one file on the upload multiple files (to a document library) option will successfully upload the URL file.  once uploaded to the document library, any user clicking on the file then gets the appropriate browser action that relates to the link.  This is actually quite a versatile thing to include into a document library as this could in theory (though I didn’t actually get around to trying it) provide access to FILE, FTP and mail-to protocol actions as well as HTTP for the typical URLs.

The developer in me still wonders what could be done with a customised list with this sort of functionality requirement in mind….


Developer Tabs and Drop Down Lists in Word 2007


It seems that drop down lists are the in thing at work right about now.  One of my colleagues dropped by with a query about creating drop down lists in Word 2007.  So this is how to go about doing it…

First of all you need to ensure that the Developer tab is enabled in Word.  To do this you need to click on the Microsoft Office button (top left) and then on the Word options button at the bottom of the office menu that appears.  In the Popular section there is a list of check boxes about the “Top options for working with Word”.  One of these check boxes is called “Show developer tab in the ribbon” … this is the one that needs to be ticked.

With the developer tab enabled and selected the controls section on the ribbon is where we need to focus our attention.  The fourth button across on the top line is the one required to create the drop down list control – a.k.a. a combo box.  Clicking this button inserts a combo box at the current cursor position.

The next step is to fully define the combo box.  By default it has one entry in it – “Choose an item”.  Select the combo box and then select properties from the controls section on the ribbon.  Alternately you can enter design mode (from the controls section on the ribbon) and right click to get to the combo box properties.

The properties dialog provides a quick and easy way to not only add and remove items from the combo box’s list of selectable items, but also to name it something meaningful using the title.

Once you’re done editing the document, you then make it into a form by selecting “protect document” on the protect group on the developer ribbon and then selecting “Filling in forms” from the editing restrictions option.

The problem however seems to be that when you set the protection options in the properties of the combo box you can stop it from being deleted, you can stop it from being edited (at all!), but you can’t seem to stop it from having an option overtyped.  Seems a little bit odd and this is my one outstanding issue with the control.


Excel – Special Names in Dynamic Lists


I posted earlier about a better way for producing dynamic lists in Excel.  One of the “tricks” I used to simplify things was using the text values of the items in a primary list to identify a secondary list to use.


Define the drop down lists

Define the drop down lists

Here We have three lists with the options set as named ranges called “Type”, “Fruit” and “Vegetable”.  Using data validation, drop down lists and Excel’s INDIRECT function means that if “Vegetable” is selected from a drop down linked to the “Type” list as a primary list, a related secondary list drop down will dynamically link to the “Vegetable” named range to validate the drop down.

This eliminates the need to do any complex lookups, but complex lookups look like the option when confronted with a primary list that contains entriess that would be invalid as names for a named range.

For example if we were looking at say UK regions and cities then an entry of “North Yorkshire” in the primary list would cause an issue as it contains a space which is an invalid character in an Excel name.

My solution is to replace the space in the naming of the range with an underscore.  This obviously won’t match to the entry in the primary list but we can use a quick substitution to accommodate this.

Simply put we might have something like in the data validation list entry for a cell linked to cell A2:


This would simply replace the spaces with underscores and provide the valid name for Excel to use.

If we’re using the full validation option from my previous post on data validation, then this substitute would be moved into the function of the working area.  So using the same example as the previous post:

Cell D4 would now contain the following function:

=IF(B4=””,””,SUBSTITUTE(B4,” “,”_”))

Cell C4 would (still) contain the following for its data validation list source:


So we can still avoid complicated lookup functions and still keep things relatively simple and instantly readable.


Excel – Dynamic Drop Down Lists with Full Validation


This post has now been migrated to

Follow this link to go directly to the article.