This post has now been migrated to ThoughtAsylum.com.
Archive for the ‘Scripting’ Category
Top FlagIT Posts of 2009
01/01/2010It’s been about a year since I started blogging here and whilst it was primarily supposed to be a site for quick useful tech-related posts several have grown into far larger posts. Whilst some posts have remained somewhat quiet on the number of reads others have generated far more interest than I could have expected.
It’s actually five more days until this blog is a year old, but I thought that new year’s day might be as good an opportunity to review what the top ten posts were. This has been based on the number of visits from more than six and a quarter thousand since the blog was created.
10. Reclaim more Memory on a Windows Mobile Device
Posted: 04/04/2009
Reads: 95 (Daily Average 0.35)
Windows mobile phones have a finite amount of memory and like any operating system (particularly M$ ones?) memory leaks can stop memory being released. This post highlights how to resolve some storage issues with the Mobile Opera browser and a useful tool for reclaiming that memory lost to leakage.
9. Simple Template Workaround for Evernote
Posted: 21/11/2009
Reads: 95 (Daily Average 2.32)
A relatively recent post about one of my favourite pieces of software (Evernote) and how to use a simple bit of scripting to create templates in Evernote.
8. A Ceiling Function for VBScript
Posted: 31/07/2009
Reads: 133 (Daily Average 0.86)
This post includes some VBS code to round a number off to the greatest integer.
7. Developer Tabs and Drop Down Lists in Word 2007
Posted: 17/03/2009
Reads: 135 (Daily Average 0.47)
This post explains how to access the developer tab in Microsoft Word 2007 and briefly discusses some of the shortcomings of the drop down lists it offers.
6. VBScript – Count Occurrences in a Text String
Posted: 30/07/2009
Reads: 148 (Daily Average 0.95)
Another simple bit of VBS code that counts the number of instances of a string of text within another string of text.
5. Omnia to Oblivia (and back)
Posted: 05/04/2009
Reads: 151 (Daily Average 0.56)
I have a Samsung Omnia mobile phone and have had a number of issues with it. This describes how I resolved one of the most difficult issues with the file system constantly mounting and dismounting a storage device.
4. A Flexible Progress Window in VBScript
Posted: 19/07/2009
Reads: 169 (Daily Average 1.02)
Another popular bit of VBS code is some that builds a customisable progress window based around dynamically updating a web page that is automatically loaded into a browser. The progress window allows the user to display messages and progress bars.
Posted: 28/03/2009
Reads: 278 (Daily Average 1.00)
The most popular VBS code post on the blog in the past year this one shows how to access a common dialog to select a folder and pass this back to the main code.
Posted: 29/07/2009
Reads: 1172 (Daily Average 7.51)
At this point we see a big change in the number of reads of the posts. This particular post’s popularity is most likely related to the popularity of Spotify. The post describes a way in which an iPhone emulator can be used on a PC running Microsofrt Windows PC to control Spotify installed on an Apple Mac.
1. Excel – Dynamic Drop Down Lists with Full Validation
Posted: 15/03/2009
Reads: 1272 (Daily Average 4.36)
At the top spot is a post relating to Microsoft Excel. It looks at how to overcome some of the limitations on dynamically populated drop down lists. There are a number of other Excel related posts on the blog that people may find useful in combination with this and it has certainly made quite an impact with some of the spreadsheets I’ve developed for colleagues at work. It seems as though that popularity is spreading to hundreds of others across the Internet.
So please have a look through these top ten (and others if you like) and I’d encourage you to leave a comment or rate the post. If you like the content why not subscribe to one of the RSS feeds?
Simple Template Workaround for Evernote
21/11/2009This post has now been migrated to ThoughtAsylum.com.
Excel: Last Entry
28/08/2009I was challenged to work out a little problem someone was having with Excel today. The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.
The task was to pick the last entry from a vertical list of cells. The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open. The algorithm I chose was based on a list length and vector approach.
So to start with I needed to know how many entries were in the list. The COUNTA function will provide this. This in effect tells us how many rows we have to step down to get to the last entry. The OFFSET function allows us to carry out these steps based on the first entry in the list. Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.
The screen shot illustrates how this is built up for a list in column A starting at cell A1. The function we would use is simply =OFFSET(A1,COUNTA(A:A)-1,0,1,1)
However this does give us a #REF! if the list has no entries – which looks like an error if you give someone a blank list (essentially because it is). So if you need to account for this, just include an IF.
=IF(COUNT(A:A)=0,””,OFFSET(A1,COUNTA(A:A)-1,0,1,1))
Send Web Cam Pictures to EverNote with SnapIT
23/08/2009Since Google Notebook went out of development earlier this year I’ve made the migration over to EverNote. It has many cool features one of the coolest being the ability to upload images and have EverNote run OCR to index the content of the image. The mobile phone EverNote applications allow a phone’s camera to be used to take a snapshot image and to upload it to EverNote over a 3G connection. Unfortunately the Windows desktop application has no functionality for capturing images in the same way through a web camera and with the proliferation of netbooks I feel that this is a feature that is sadly missing – well for me anyway. So after a bit of research I embarked on a quick weekend VB.Net project to put together a little application that could do this – and so I created SnapIT.
SnapIT allows you to take a photo with an attached or built in camera (by pressing the “Capture Image” button) and save it (by pressing the “Store Image” button) to a specified folder as a JPEG with a file name based on the time at which it was taken. It also allows you to send the image off to an EverNote database with options to specify which notebook it gets sent to (it will of course default to the default notebook), a title and tags (separated by commas). Where the image ends up is determined by the checkbox options that have been selected (next to the “Store Image” button).
It’s a standalone executable and stores a few settings in an INI file it creates in the same folder. When you first use it (assuming you want to save images or send them to EverNote) you will need to set a few file/folder paths. This can be done from File > Settings. You need to set a folder to store images in, the location of ENScript.exe (used to send the image to the database) and the location of the EverNote database file to which images should be sent.
This is just something I threw together for my own use and given that I wrote it in just a day or so it really hasn’t gone through much testing, so please keep that in mind when using it. That being said if you find a bug then add a comment to this blog post and if I can reproduce it I may just find the time to fix it and post a new version.
ENScript – Scripting Evernote in Windows
21/08/2009This post has now been migrated to ThoughtAsylum.com.
Skype and Web Servers
08/08/2009I briefly had a problem with the web server that’s running my personal wiki yesterday. Try as I might it just wouldn’t start-up. I hadn’t had a problem before and after three or four attempts at clicking the start option I took a step back and surveyed my machine – a useful option in many circumstances.
I spotted the culprit straight away as I had come across the issue before – Skype. When Skype starts up if the default port (51907 on my current instance of Skype) is unavailable (because another application is using it or more likely a firewall is blocking it) it tries to set-up on a port that is more likely to be available – 80 or 443 which are used by web servers for insecure and secure traffic respectively.
Whilst there is an advanced option in Skype to force it away from these two alternative ports often there are no other convenient ones to choose from. This is because it is good practice security to block unused ports and Skype isn’t always a top runner so to speak on ports to have open.
The moral of the story is simple – start your web server before you start Skype.
However this got me to wondering what else might pick a port that could conflict and how would you know what it was without just systematically working through all the applications and services you have running … which for me could be a large number. My solution was to use some DOS commands. NETSTAT can be used to examine what ports are in use by what processes. TASKLIST can be used then to identify the executable.
To smooth things over a little I’ve concocted a little script to automate this process. It’s really just a bit of a nicer front end to the whole thing and whilst several command prompt windows may briefly flash up as it runs, the script will take in the information from the commands and process them to filter out the pertinent information.
Option Explicit Dim objCommandPort Dim astrResults Dim astrInfoLine Dim strOutput Dim strPortCommand Dim strPID Dim strApps strPortCommand = "netstat -ano" set objCommandPort = New clsDOSCommandExecutor objCommandPort.ExecuteCommand(strPortCommand) strOutput = objCommandPort.GetOutput astrResults = split(strOutput, vbCrLf) strApps = "PORT: APPLICATION" & vbCrLf AssessPort "80" strApps = strApps & vbCrLf AssessPort "443" MsgBox strApps, vbInformation + vbOKOnly, "Applications using web server ports" Sub AssessPort(p_strPort) Dim intCounter For intCounter = 1 to Ubound(astrResults) If inStr(astrResults(intCounter), ":" & p_strPort) > 0 Then astrInfoLine = split(astrResults(intCounter)) strPID = astrInfoLine(ubound(astrInfoLine)) strApps = strApps & vbCrLf & p_strPort & ": " & AppOnPort(strPID) End If Next End Sub Function AppOnPort(p_strPID) Dim objCommandPID Dim strPIDCommand Dim strPIDOut Dim astrPIDOut set objCommandPID = New clsDOSCommandExecutor strPIDCommand = "tasklist /fi ""PID eq " & p_strPID & """" objCommandPID.ExecuteCommand(strPIDCommand) strPIDOut = Right(objCommandPID.GetOutput, len(objCommandPID.GetOutput) - inStrRev(objCommandPID.GetOutput, "=" & vbcrlf)) astrPIDOut = split(strPIDOut) AppOnPort = Replace(astrPIDout(0), vbCrLf, "") End Function Class clsDOSCommandExecutor Dim objShell, objExec Dim strCommand Dim strError Dim objError Dim objOutput Dim strOutput Sub ExecuteCommand(p_strCommand) strCommand = "cmd /c " & p_strCommand Set objShell = CreateObject("Wscript.Shell" ) objShell.Exec(strCommand) Set objExec = objShell.Exec(strCommand) Do Until objExec.Status Wscript.Sleep 200 Loop Set objError = objExec.StdErr strError = objError.ReadAll Set objOutput = objExec.stdOut strOutput = objOutput.ReadAll End Sub Function GetOutput() GetOutput = strOutput End Function Function GetError() GetError = strError End Function Function Failed() If strError = "" Then Failed = false Else Failed = true End If End Function End Class
DOS Command Class for VBScript
08/08/2009This post has now been migrated to ThoughtAsylum.com.