Tuesday, May 27, 2014

Reading Excel Files in Classic ASP

Recently I had the need to source data from Excel files in the old Classic ASP platform. There are some good resources online which can help you with this, but I thought I'd log my little experience here which may hopefully expedite the process for someone else someday :)

In my experience most people use VB script in their ASP environment,. the past few years I have grown to prefer using JScript. I'll provide my testing in both.

A Little Environment Preface

In my examples I will have a file called unlocodes.xlsx placed in the directory c:\temp\
The content of the Excel file looks like this:


Here is a barebones ASP sample connecting to the Excel file.

This resulted in:

ASP (JScript)

Here is a JScript sample connecting to the Excel file. In my current environment I never use serverside JScript to render HTML, rather only to serve in a JSON based API format. I trimmed down the scaffolding into the bare necessities: an Excel interface and a JSON polyfill that works in ASP JScript.

This resulted in:

Some Extra Notes

I crossed paths with two errors, both of which were resolved by simply choosing the correct connection string.
This error:
ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

and this error:
Microsoft JET Database Engine error '80004005'

External table is not in the expected format.

The ConnectionStrings.com website is a great resource for finding a connection string compatible with your installed version of Excel. I found that on my machine with Excel 2010 this connection string worked:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Whereas on our production server we have 2013 installed:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

If you continue to have problems finding the correct driver, or it complaining it's not installed, then be sure to download and install the Microsoft Access Database Engine 2010 Redistributable. This includes the latest ACE drivers which come in 32 and 64 bit flavors. For posterity you may want to try install the 64bit version in command line using the follwoing syntax:
AccessDatabaseEngine_X64.exe /passive

Wednesday, May 7, 2014

Injecting CSS with Javascript

Why injectCSS? Why make it? What is it solving?

  • The primary goal was to create an easy way to include multi-line (lots) of CSS using Javascript.
  • Can reduce number of files in a JS plugin or project (My goal was one).
  • A test run at jsperf.com shows that injecting CSS rules for a vast number of elements is very fast.
There are already some existing CSS injection tools, but I wanted something dead-simple. There is a great library called VeinJS, which does so much more than mine in many different ways. But after perusing through the examples I knew that I would have to dismantle my CSS too much. What I wanted was something like what you will see in the example below.

Example Usage

With RequireJS

With no dependencies


Tested successfully on:

  • Chrome (+mobile)
  • Firefox
  • Safari (+mobile)
  • IE 10+
Due to the older IE browsers not allowing the innerHTML property to be set on certain elements, this will not work in them.


The code is on GitHub. Download, use and modify as you please.