Storing data in a Base64 encoding using VBA

Here’s some code that I’m pretty proud of, its a nifty piece of code that allows to store any kind of data in a simple textbased container. It uses a custom Base64 encoding to store integers, floats, strings, booleans or blobs in a text block and adds some CRC checks and even some repair capabilities.

First question that probably comes to mind; what the heck do you want to use something like this for? Well, I actually had two cases where I used it;

  1. When a recurring task in Outlook is finished, a new task is automatically created. Yet any custom properties that task has, will not be copied to the new task. Hence that data is lost. As the text body is moving over, storing the data in the text body is a workaround to pass the data on. But I didn’t like it to be editable by the user, so some sort of INI like structure wasn’t ok, it had to use some sort of encoding en needed to have some verification on tampering.
  2. Working for a customer that uses very strict firewall rules turned my life into email hell. Word or Excel documents would pass, but not much more. Content of zip files was being checked, and encrypted zips we’re not allowed. By adding the binary files (just MS Project .MPP files in this case) in encoded format to a word document, the files could pass. As everything is in VBA, on the receiving end, the data and the logic required to decode the data we’re all in the same Word document (download the example below).

NOTE: Sending files through firewalls this way is a way to circumvent security measures, please be carefull when using this. If you choose to use it, you will bear sole responsibilty for it. Use at your own risk!
[ad name=”468×60 Banner”]

Features of the class

  • Text based startheader
  • Text based endheader
  • merging of multiple datablocks into 1
  • includes basic CRC checks to verify data integrity and even make some repairs
  • survives emailing with lines being broken up
  • Store as many values as you want
  • Values can also be entire files, directly being loaded from/saved to disk (date/timestamps will be preserved)
  • custom Base64 en/decoding with CRC checks

 

How it works

The code consists of two classes, the main class does the encoding and contains a collection of items (bools, strings, blobs, etc.) that are included in the data block. The items in the collection are based upon the second class.

When working with the class, you can just provide the full text of the container (for example an email), specific start and end markers are used to find the datablock inside the text. When adding or changing data, only the block between the markers is updated, the remainder is left unchanged. Here’s an example of what it looks like;
In case of an email, this would be the email text, below you see the two “DO NOT EDIT …” lines, which are the markers between which the data is located.
______________________DO_NOT_EDIT_ANYTHING_BELOW_THIS_LINE______________________
1J00000GppLQtx1000VHpC7SV46OZK6OVBrQVZ6SVRrQiS2SV8LOVJLQkGMT601000FHCS4JoJIBtwKw
@1pGic6Rb11000FHCS4JoJIBtw`HFSZAe1rOYGCwrkfTY3JZA23Fwers0tmE10kzNzEs00WH6`ZG04VX
1400810H00kzWfCo5WMPa100Cp40e0000V0094k30800000500026604080000050002P6V40B0000Fs
10F000F0l0V00000F0008sP0m0V00000J0008sQ0w0V00000F00088h0I0k0000010V00sxWd101000D
04000VVsen1070008k0000VT00000kWu0000800000000000000000000000000000000000000000FO
000000000000000000000000000000000000000000000000000000000000000000000000000000kz
000000000000000000000000000000000000000000000000000000000000000000000000000000kz
000000000000000000000000000000000000000000000000000000000000000000000000000000kz
000000000000000000000000000000000000000000000000000000000000000000000000000000kz
oePiOq9NqstQVdYOjnBYFUGkOmsoW7wMWHzxpqUSJQ3umWd34peohEnNRUeiszD5a00McR7RZ38Y67@D
______________________DO_NOT_EDIT_ANYTHING_ABOVE_THIS_LINE______________________
Any text below the data block is also preserved. In the block you can see that the last column and the last row contain the CRC data, just by the looks of it.

External references used by the project;

[ad name=”468×60 Banner”]

Example and downloads

The download (downloaded 1543 times) contains a Word document which includes all VBA code (including the other code required; Chip Pearsons, and some generic stuff found elswhere on my site). Try the example (its the firewall passing application), if you save the file included in it, you’ll get a nice picture of me!

Be Sociable, Share!

5 thoughts on “Storing data in a Base64 encoding using VBA

  1. Thank you for this excellent code! Let me explain our use case; we have data exchange over some API’s in xml, they contain creative data, eg. jpg, bmp etc. which needs encoding. Some of our clients do not like third party dll’s (which we have available), this allows for standard use of excel and access. This really helps!

  2. Hi there,

    I havent been programming for around 5-10 years now, and found this code awesome, however stepping through it I’m trying to see where the file contents are encoded (might be dumb question) so that I can use the data to do something with.

    My use is to read a file location string from a database and then use the b64 data stream as an attachment to an MMS message – yup – it is probably easier in other languages, but I’m stuck in a VBA timewarp for now. This is then passed to a PHP page as a parameter to send the message via web service.

    What I’m looking for is something that can read like:
    surl = surl & “&Att1=” & GetFileDatab64(sFileLoc)

    Where sFileLoc is the file being sent.

    Many thanks for help, and apologies if this is just me missing the obvious.

    • The en/decoding is done in clsEmbeddedData, in two steps; the available data (in blocks), and the Base64 on an individual line. When encoding, all data elements are converted to string values and concatenated to one big string.
      When the string is encoded, function EncodeDataBlock chops it into individual lines and blocks, it adds a CRC value to the end of each line (horizontal CRC) and after a set amount of lines it adds a CRC line to the block (vertical CRC). So far everything is just a regular string.
      Each individual line (string) is then encoded to (a non-standard form of) Base64 using the function EncodeString.

      All the lines together are the entire datablock, which will get a custom header and footer to be able to identify the block inside a larger textblock.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Subscribe without commenting