Monday, October 26, 2009

Regular Expressions Regex

I mostly use RegexBuddy to create and test my regular expressions, but I just came across some nice free online tools RegExr , RexV, JavaScript Regex Generator and Nregex to create and test regular expressions. Also useful, a toolbox, RegExp Tools and some examples of some commonly required regular expressions...

Java regex tester

Friday, October 16, 2009

Dropbox - daily usage

Dropbox is a free online service to easily synchronize and backup your data. It can be very useful and it has some very nice features, but it has some limitations too.

Dropbox features:

  • 2GB free online storage, easy registration.
  • Easy sync to different computers / Macs.
  • Files are always reachable using the Dropbox website.
  • Dropbox keeps 30 days version and deleted files history.
  • A public folder with direct static file url's makes it possible to host your site on Dropbox server.
  • Sharing of folders between Dropbox accounts is possible.

Dropbox limitation:

  • No filtering on files / folders is possible.
  • For each account, one folder is synchronized. You need to use Junctions (hardlink NTFS shortcuts (easy Junction explorer extention)) if you want to include external folders in the sync.
  • No default support for multiple account synchronization. You need to use the portable Dropbox (see information below) if you want to synchronize multiple accounts from the same computer at the same time.
  • No option to make Dropbox always request for account password on startup (would be useful for the portable version).

Personal usage scenario's:

  • Synchronization of my AI Roboform data so I have all my login's everywhere. I combine this with the Roboform2go on my USB stick and Dropbox portable to make sure everything is kept in sync.
  • Hosting of files for websites. Instead of having to upload them to different free hosts with ads, I can now use the Dropbox space for easy hosting. Even complete websites are usable when hosted within the 'public' folder of a Dropbox account.
  • Combination of FreeOTFE portable to make sure my USB stick portable Dropbox account is kept save. I place all Dropbox files within a FreeOTFE secured file, since else when losing USB stick, anyone could have access to my Dropbox account by just starting up the portable Dropbox application.
  • Some other tips and tricks for Dropbox usage from LifeHacker. The 'start torrent from anywhere' trick is nice!
  • Since I use my personal SVN I wanted to make a combination of the automatic Dropbox synchronization coupled to the full control SVN synchronization for development projects. By using the Junctions I could link the SVN folders into Dropbox. Now I have an auto sync of files and folders, but I can manually sync with SVN to have an extra backup and history tracking with full control. The downside of this is that all hidden '.svn' folders are kept in sync too within Dropbox and this can take a lot of your Dropbox space. With the selective sync option in Dropbox, you can disable the syncronisation of the .svn folders to save space. But be carefull, when deselecting .svn folders in the Dropbox configuration, it will remove those folders from your local system. So it’s best to first create some dummy empty .svn folders, next disable the sync of these folders and then copy the real .svn folder at the correct location.

Dropbox Portable installation:

DropboxPortableAHK is now available. This makes the use of Dropbox Portable much easier. All download/configuration is now automated and very userfriendly. Just download from the developer website: http://nionsoftware.com/dbpahk/overview

Update 09/01/2011: New version of the Dropbox Portable framework (5.3.4). But this new framework requires a relink! The new version has easier update (just copy official Dropbox setup file in the update folder). Cleanup of blog and added extra info on installation.

Update 20/01/2011: added info from comments to change path in config.db

Update 03/04/2011: link to new DropboPortableAHK version, no more manual tweaks required.

Sunday, October 11, 2009

Keep your batteries in good shape

I red a good article on how to keep your batteries in good shape: Dutch, Babelfish translated English version

Top tips:

  • Make sure to recharge long enough before first usage

  • Always use the original or exactly matching charger

  • Nickel Cadmium (NiCd) batteries should be completely empty before recharging (a battery memory effect will shorten the battery live)
    • These batteries are most often used as AAA or AA rechargeable batteries or older mobile phones

  • Lithium Ion batteries should never be completely empty before recharging (no battery memory, but very sensitive to higher temperatures while charging)
    • These batteries are most often used in mobile phones, pda's and notebooks

  • It can help to put your batteries in a cool environment when you won't use them for some time.

Thursday, October 1, 2009

Folder structure creator - Excel VBS

If you need to create a lot of folders and subfolders, Excel_2007.jpgwith some specific structure, it can be usefull if you can use the power of Excel to make up your folder names and structure. All kind of easy and quick formulas can be used, and once the strucuture is set up, you can easily create the empty folder structure with just one click by using this little VBS macro. The base folder used will depend on the location of the Excel file, so make sure it's saved or copied in the correct folder.

Sub CreateFolderStructure()
'Create folder for all vlues in current sheet
'folders will be created in folder where the excel file was saved
'folders will be created from first row, first column, until empty row is found
'Example expected cell structure: (data starting in current sheet, column A, row 1)
'folder1 subfolder1 subsubfolder1
'folder2
'folder3 subfolder3
'...
'this will result in:
'\folder1\subfolder1\subsubfolder1
'\folder2
'\folder3\subfolder3
'...
Set fs = CreateObject("Scripting.FileSystemObject")
For iRow = 1 To 65000
pathToCreate = ActiveWorkbook.Path
For iColumn = 1 To 65000
currValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value
If (currValue = "") Then
Exit For
Else
pathToCreate = pathToCreate & "\" & CStr(currValue)
'MsgBox (pathToCreate)
folderToCreate = pathToCreate
If Not (fs.FolderExists(folderToCreate)) Then
fs.CreateFolder (folderToCreate)
End If
End If
Next
Next
End Sub


The Excel sheet with the macro can be downloaded here. Before running the macro make sure the rows and columns of the active sheet are filled in correctly. Next simply run the macro by using the button. foldercreatorexcel1foldercreatorexcel2 foldercreatorexcel3foldercreatorexcel4



If you created to many empty folders by accident, you can easily remove them again using this little tool: Remove Empty Directories



Update 13/11/2009: Modified the Excel VBS script to let you navigate to the desired base folder upon launching the macro, so the Excel file may now be saved at any location, the base folder will have to be specified upon launching the macro.



Update 21/04/2012: Someone commented the VBS code is not working correctly when using some special characters. This is because some characters are not supported by Windows to be used in a file or folder name.



FolderChar



I updated the VBS code in the Excel sheet to make sure these special characters are removed before trying to create the folders.



The Excel sheet is updated, also an Excel template is available.



An example with special characters and the resulting folders created:



FolderSpecialCharsExample



The new VBS code used is:



Sub CreateFolderStructure()
'Create folder for all vlues in current sheet
'folders will be created in folder where the excel file was saved
'folders will be created from first row, first column, until empty row is found
'Example expected cell structure: (data starting in current sheet, column A, row 1)
'folder1 subfolder1 subsubfolder1
'folder2
'folder3 subfolder3
' subfolder4
'...
'this will result in:
'\folder1\subfolder1\subsubfolder1
'\folder2
'\folder3\subfolder3
'\folder3\subfolder4
'...
baseFolder = BrowseForFolder
If (baseFolder = False) Then
Exit Sub
End If
Set fs = CreateObject("Scripting.FileSystemObject")
For iRow = 2 To 6500
pathToCreate = baseFolder
leafFound = False
For iColumn = 1 To 6500
currValue = Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value, ":", ""), "*", ""), "?", ""), Chr(34), ""), "<", ""), ">", ""), "|", ""))
If (currValue = "" And leafFound) Then
Exit For
ElseIf (currValue = "") Then
parentFolder = FindParentFolder(iRow, iColumn)
parentFolder = Replace(Replace(Replace(Replace(Replace(Replace(Replace(parentFolder, ":", ""), "*", ""), "?", ""), Chr(34), ""), "<", ""), ">", ""), "|", "")
If (parentFolder = False) Then
Exit For
Else
pathToCreate = pathToCreate & "\" & parentFolder
If Not (fs.FolderExists(pathToCreate)) Then
CreateDirs (pathToCreate)
End If
End If
Else
leafFound = True
pathToCreate = pathToCreate & "\" & currValue
If Not (fs.FolderExists(pathToCreate)) Then
CreateDirs (pathToCreate)
End If
End If
Next
If (leafFound = False) Then
Exit For
End If
Next
End Sub

Function FindParentFolder(row, column)
For iRow = row To 0 Step -1
currValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column).Value
If (currValue <> "") Then
FindParentFolder = CStr(currValue)
Exit Function
ElseIf (column <> 1) Then
leftValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column - 1).Value
If (leftValue <> "") Then
FindParentFolder = False
Exit Function
End If
End If
Next
End Function


Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

Sub CreateDirs(MyDirName)
' This subroutine creates multiple folders like CMD.EXE's internal MD command.
' By default VBScript can only create one level of folders at a time (blows
' up otherwise!).
'
' Argument:
' MyDirName [string] folder(s) to be created, single or
' multi level, absolute or relative,
' "d:\folder\subfolder" format or UNC
'
' Written by Todd Reeves
' Modified by Rob van der Woude
' http://www.robvanderwoude.com

Dim arrDirs, i, idxFirst, objFSO, strDir, strDirBuild

' Create a file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Convert relative to absolute path
strDir = objFSO.GetAbsolutePathName(MyDirName)

' Split a multi level path in its "components"
arrDirs = Split(strDir, "\")

' Check if the absolute path is UNC or not
If Left(strDir, 2) = "\\" Then
strDirBuild = "\\" & arrDirs(2) & "\" & arrDirs(3) & "\"
idxFirst = 4
Else
strDirBuild = arrDirs(0) & "\"
idxFirst = 1
End If

' Check each (sub)folder and create it if it doesn't exist
For i = idxFirst To UBound(arrDirs)
strDirBuild = objFSO.BuildPath(strDirBuild, arrDirs(i))
If Not objFSO.FolderExists(strDirBuild) Then
objFSO.CreateFolder strDirBuild
End If
Next

' Release the file system object
Set objFSO = Nothing
End Sub


11/10/2012: I updated the VBS code in the Excel sheet to make sure these special characters are removed before trying to create the folders. Extra input information and validation is added to make sure invalid characters can not be used. The Excel sheet is updated, also the Excel template



08/08/2013: Updated sheets, added a trim to remove spaces at begin and end of cell value, since this could result in macro exception (see comments)



17/02/2014: I’ve extended this workbook with a new sheet in which the nested folder structure of the filesystem can be imported. Each folder name will be stored in a separate cell respecting the nested structure



The new VBS code is:



  
Sub ImportFolderStructure()
'Import folder structure starting from selected base folder
'each subfolder will be stored in a separete cell
'eg:
'Folder 1|Subfolder1|SubSubfolder1
'Folder 2|Subfolder2
'Folder 3|Subfolder3|SubSubfolder3
'...
    Application.ScreenUpdating = False
    baseFolder = BrowseForFolder
    If (baseFolder = False) Then
        Exit Sub
    End If
    Application.StatusBar = "Folder structure below " & baseFolder & " will be stored in the sheet " & ActiveCell.Worksheet.Name
    StoreSubFolder baseFolder, 1, 0
    Application.StatusBar = "Folder structure below " & baseFolder & " has been stored in the sheet " & ActiveCell.Worksheet.Name
    Range("A2").Select
    Application.ScreenUpdating = True
End Sub

Sub StoreSubFolder(baseFolderObj, ByRef iRow, ByVal iColumn)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set folderBase = fs.GetFolder(baseFolderObj)
    Set folderBaseSubs = folderBase.SubFolders
    iRow = iRow + 1
    iColumn = iColumn + 1
    For Each subFolder In folderBaseSubs
        Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value = subFolder.Name
        StoreSubFolder subFolder, iRow, iColumn
    Next
End Sub

Sub ClearImportData()
    Application.ScreenUpdating = False
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    Range("A2").Select
    Application.ScreenUpdating = True
End Sub

Sub CreateFolderStructure()
'Create folder for all vlues in current sheet
'folders will be created in folder where the excel file was saved
'folders will be created from first row, first column, until empty row is found
'Example expected cell structure: (data starting in current sheet, column A, row 1)
'folder1    subfolder1  subsubfolder1
'folder2
'folder3    subfolder3
'           subfolder4
'...
'this will result in:
'<currentpath>\folder1\subfolder1\subsubfolder1
'<currentpath>\folder2
'<currentpath>\folder3\subfolder3
'<currentpath>\folder3\subfolder4
'...
    baseFolder = BrowseForFolder
    If (baseFolder = False) Then
        Exit Sub
    End If
    Set fs = CreateObject("Scripting.FileSystemObject")
    For iRow = 2 To 6500
        pathToCreate = baseFolder
        leafFound = False
        For iColumn = 1 To 6500
            currValue = Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value, ":", "-"), "*", "-"), "?", "-"), Chr(34), "-"), "<", "-"), ">", "-"), "|", "-"), "/", "-"), "\", "-"))
            Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value = currValue
            If (currValue = "" And leafFound) Then
                Exit For
            ElseIf (currValue = "") Then
                parentFolder = FindParentFolder(iRow, iColumn)
                If (parentFolder = False) Then
                    Exit For
                Else
                    pathToCreate = pathToCreate & "\" & parentFolder
                    If Not (fs.FolderExists(pathToCreate)) Then
                        CreateDirs (pathToCreate)
                    End If
                End If
            Else
                leafFound = True
                pathToCreate = pathToCreate & "\" & currValue
                If Not (fs.FolderExists(pathToCreate)) Then
                    CreateDirs (pathToCreate)
                End If
            End If
        Next
        If (leafFound = False) Then
            Exit For
        End If
    Next
End Sub

Function FindParentFolder(row, column)
    For iRow = row To 0 Step -1
        currValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column).Value
        If (currValue <> "") Then
            FindParentFolder = CStr(currValue)
            Exit Function
        ElseIf (column <> 1) Then
            leftValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column - 1).Value
            If (leftValue <> "") Then
                FindParentFolder = False
                Exit Function
            End If
        End If
    Next
End Function

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level
    
    Dim ShellApp As Object
    
     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    
     'Destroy the Shell Application
    Set ShellApp = Nothing
    
     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select
    
    Exit Function
    
Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
    
End Function

Sub CreateDirs(MyDirName)
' This subroutine creates multiple folders like CMD.EXE's internal MD command.
' By default VBScript can only create one level of folders at a time (blows
' up otherwise!).
'
' Argument:
' MyDirName   [string]   folder(s) to be created, single or
'                        multi level, absolute or relative,
'                        "d:\folder\subfolder" format or UNC
'
' Written by Todd Reeves
' Modified by Rob van der Woude
' http://www.robvanderwoude.com

    Dim arrDirs, i, idxFirst, objFSO, strDir, strDirBuild
    ' Create a file system object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    ' Convert relative to absolute path
    strDir = objFSO.GetAbsolutePathName(MyDirName)
    ' Split a multi level path in its "components"
    arrDirs = Split(strDir, "\")
    ' Check if the absolute path is UNC or not
    If Left(strDir, 2) = "\\" Then
        strDirBuild = "\\" & arrDirs(2) & "\" & arrDirs(3) & "\"
        idxFirst = 4
    Else
        strDirBuild = arrDirs(0) & "\"
        idxFirst = 1
    End If

    ' Check each (sub)folder and create it if it doesn't exist
    For i = idxFirst To UBound(arrDirs)
        strDirBuild = objFSO.BuildPath(strDirBuild, arrDirs(i))
        If Not objFSO.FolderExists(strDirBuild) Then
            objFSO.CreateFolder strDirBuild
        End If
    Next

    ' Release the file system object
    Set objFSO = Nothing
End Sub


The Excel file can be downloaded as XLS or XLT (template for easy reuse). My Excel sheet with combined macro’s has been updated as well, see this blog post.











Update 27/05/2014: cleanup of the cell types (some cell were saved as type ‘Scientific’ resulting in some strange representation after import of folders named with numbers, Thanks to Jean for reporting)