VBScript – Convert Image To Base 64

Last week I wrote a function which takes a file path and returns the base64 PNG code for that image. Since it isn’t straight-forward for everyone as to how to convert that JScript code to VBScript I decided to do just that:

Public Function convertImageToBase64(filePath)
  Dim inputStream
  Set inputStream = CreateObject("ADODB.Stream")
  inputStream.Open
  inputStream.Type = 1  ' adTypeBinary
  inputStream.LoadFromFile filePath
  Dim bytes: bytes = inputStream.Read
  Dim dom: Set dom = CreateObject("Microsoft.XMLDOM")
  Dim elem: Set elem = dom.createElement("tmp")
  elem.dataType = "bin.base64"
  elem.nodeTypedValue = bytes
  convertImageToBase64 = "data:image/png;base64," & Replace(elem.text, vbLf, "")
End Function

Feel free to use this code in your own projects. 😎

VBA – Encode URL Query Parts In Excel

Have you ever had a column text for which you wish you could easily create links? For example, let’s say that you have a column of search words (or phrases) that should link to the Google page for each search. How would you do it? First of all, you would probably want to be able to build the URL in a separate column. Unfortunately, Excel doesn’t provide a way of encode URL parameters. Therefore, you can use the following as your own UDF:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function

That function will take in one string and output the equivalent as an encoded URL query part. That function should be the equivalent of PHP’s urlencode() function. Tomorrow, we will see how this function can actually help you to be able to create links for an entire column.

POW Answer – Explain That SQL #1

To answer last week’s POW, the purpose of the SQL was to generate a random string of letters and numbers. I actually wrote two different JavaScript function that can produce the same results. The following is the slightly more straight-forward solution:

function randomChars(len) {
  for(var n, s = ""; --len;) {
    n = parseInt(Math.random() * 62);
    s = String.fromCharCode(n + (n < 26 ? 65 : n < 52 ? 71 : -4));
  }
  return s;
}

The next version is a bit harder to follow because it involves recursion:

function recRandomChars(len) {
  if(!len){ return ""; }
  var n = parseInt(Math.random() * 62);
  return recRandomChars(--len)
    + String.fromCharCode(n + (n < 26 ? 65 : n < 52 ? 71 : -4));
}

One practical application of this is to generate a password with random characters.