VBScript to read binary files and load data into Excel - Life in the old dog yet

Why bother?

The snag with VBScript is that it is ugly, old, and very very useful. I still find myself using this ancient technology from time to time, and wanted to post an example of the sort of thing it can still be used for if you want something 'quick and dirty'. Here is an example of doing a couple of things we don't always associate with VBScript: reading binary files and loading data into Excel.

Reading binary files

VBScript can read binary files because it can consume COM objects and ADO is COM, and it can read binary files. Here is a function which does that very thing:

function GetGifComment(gifFilePath)
    dim inStream,buff,commentLen,commentStr,myIndex
    dim myByte,myByteValue,myCharacter

    set inStream=WScript.CreateObject("ADODB.Stream")

    inStream.Open
    inStream.type=1

    inStream.LoadFromFile gifFilePath 

    buff=inStream.Read()
    inStream.Close
 
    commentStr=""
    for myIndex = 1 to 6
      ' Extract 1 byte from the buffer
      myByte = MidB(buff,myIndex,1)
      ' Gets its numeric value
      myByteValue = AscB(myByte)
      ' Convert that numeric value into a character
      myCharacter  = Chr(myByteValue)
      ' Append that character to the string
      commentStr  = commentStr & myCharacter
    next
    GetGifComment = commentStr
end function

The above function uses a ADODB.Stream object. I guess these were designed for loading files into databases; however, they work nicely for reading small binary files from VBScript. I suspect the entire file is read into memory so I would not try loading huge files this way. Once we have the file in memory, we can get at its contents in a local variable via its read method (again, not efficient - but that is not the point). Once the contents of the file are in a local variable, they can be inspected using the MidB, AscB, and Chr built-in functions.

Putting this to use

In the below script, I have used the WScript.Shell object to then get at the SpecialFolders enumeration which has given me access to the current user's desktop directory. I can then use Scripting.FileSystemObject to look for any GIF files on the desktop. I also directly open an Excel instance and create inside it a new workbook. The final trick is then to look for any GIF files on the desktop and use my binary file trick to read inside the file to get the GIF version type. The name of the file and the version are then listed in Excel.

Option Explicit
  Dim myShell, sendToFolder, myPicturesPath, myShortcut
  Dim fso,myFolder, myFile, fileName, comment, myExcel
  Dim myWorkbook, myRow, mySheet
  ' Find "My Pictures"
  Set myShell = CreateObject("WScript.Shell")
  myPicturesPath = myShell.SpecialFolders("Desktop")

  ' Open "My Pictures" as a folder so we can see
  ' which files are inside it
  Set fso=CreateObject("Scripting.FileSystemObject")
  Set myFolder=fso.GetFolder(myPicturesPath)

  ' Set Up Excel To receive The Data
  Set myExcel=CreateObject("Excel.Application")
  Set myWorkBook=myExcel.WorkBooks.Add
  Set mySheet=myWorkBook.Sheets(1)
  myRow=2
  mySheet.Cells(1,1).Value="Name"
  mySheet.Cells(1,2).Value="GIF Type"
  myExcel.Visible=TRUE

  ' Loop through each file found and see
  ' if its file extension is .gif
  ' If a file is a .gif file then call our function
  ' which opens it as a binary file and reads the 
  ' version label
  for each myFile in myFolder.Files
    fileName=myFile.name
      fileName=Lcase(fileName)
      if Right(fileName,4)=".gif" then
        ' Read the version label
        comment=GetGifComment(myFile.path)
        ' Place the data in the spreadsheet
        mySheet.Cells(myRow,1).Value=fileName
        mySheet.Cells(myRow,2).Value=comment
        ' Step down to the next Row
        myRow=myRow+1
                else
                WScript.echo fileName
      end if
  next

  ' Make the spreadsheet look a bit nicer
  With mySheet.Range("A1:B1").Font
    .FontStyle = "Bold"
    .Size = 12
  End With

  mySheet.Columns(1).Autofit
  mySheet.Columns(2).Autofit

  'Script ends here
 
  function GetGifComment(gifFilePath)
    dim inStream,buff,commentLen,commentStr,myIndex
    dim myByte,myByteValue,myCharacter

    set inStream=WScript.CreateObject("ADODB.Stream")

    inStream.Open
    inStream.type=1

    inStream.LoadFromFile gifFilePath 

    buff=inStream.Read()
    inStream.Close
 
    commentStr=""
    for myIndex = 1 to 6
      ' Extract 1 byte from the buffer
      myByte = MidB(buff,myIndex,1)
      ' Gets its numeric value
      myByteValue = AscB(myByte)
      ' Convert that numeric value into a character
      myCharacter  = Chr(myByteValue)
      ' Append that character to the string
      commentStr  = commentStr & myCharacter
    next
    GetGifComment = commentStr
  end function

Conclusions

Is this a really useful script by itself - I doubt it. However, the various bits of it show how a script can be really handy in those nasty system admin or data management tasks for which firing up Visual Studio and writing a full blown application might be overkill. I suspect a lot of this could also be done at least as well in PowerShell. Maybe that should be a topic for another post?