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:

ASP (VB)

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