Inno Setup Overview

January 19th, 2010 Matt J. Wilson No comments

Based on a lot of the hits to this blog, it seems like a lot of people are simply trying to understand Inno Setup. I hope this blog post will be useful; I’m not going to refer to any code here as I believe the other examples on this blog should suffice, but here is a brief outline of Inno Setup and ISTool.

First, you probably want to download a copy of Inno Setup. Inno Setup is basically a great, free alternative to other packaging solutions (InstallShield, etc.). There is a graphical, wizard interface that will suffice for building some of the basics of your project, but if you aren’t a programmer you’ll still have difficulties.

This is where ISTool comes in. ISTool adds a graphical layer on top of Inno Setup that allows non-programmers to create almost any type of script they desire in an easy-to-use UI. Even though I know how to write scripts in Inno Setup, I still use ISTool to form the basis of almost all of my scripts.

Another huge advantage of ISTool? It allows you to implement a download component into Inno Setup, which makes it very straight forward to add files to be download from the internet during an installation (view a sample of using the isxdl.dll included with ISTool).

Still have questions? Feel free to contact me.

Categories: Programming Tags:

Central Error Tracking for Deployed Systems

January 11th, 2010 Matt J. Wilson No comments

At one of my previous employers, we had several remote deployments (hundreds, actually) and a need to track remote errors in a central location; we utilized an ASP site to visually display this combined collection of errors.

I’ve taken the time to rewrite my own central error tracking mechanism from scratch. This module includes text logs that automatically rotate themselves once a file reaches a certain size (no more trying to have someone email you a 3 GB text file), useful information about what type of error occurred, and a SQL CE database that stores these errors in a local database, counting the number of incidences and whether there is fresh information to report on a schedule to the central database.

There is also a central web service that accepts these exception reporting requests and inserts them into a central SQL database for you to do with what you will (ASP website, Excel sheets with queries, etc., etc.). You can find a copy of this solution (Visual Studio 2005 written in C#) here.

Categories: Programming Tags: , ,

Another Inno Setup Example

December 9th, 2009 Matt J. Wilson No comments

I realize no one will be able to copy and paste this script to do exactly what they want, but I thought several of these items might be useful.

I developed another Inno Setup script that copies all files from a file share locally, runs a few installers (from a nested “Installers” directory that houses all the exe’s as you’ll see), sets some registry settings for an ODBC connection to a DBISAM database, and copies the .NET trust settings from a nested “Net Framework” folder, which allows me to set a “Full Trust” as needed for several applications. This setup essentially lets me put the “Net Framework” and an “Installers” folder on a disc with the EXE that this script generates, creating an easy to use install disc for setting up new workstations.

Here is the code, I hope someone finds a useful snippet or two:

#define MyAppName "Software"
#define MyAppVerName "Software Installation 2.0"
#define MyAppPublisher "My Company"

[Setup]
; NOTE: The value of AppId uniquely identifies this application.
; Do not use the same AppId value in installers for other applications.
; (To generate a new GUID, click Tools | Generate GUID inside the IDE.)
AppId={{4D2577F9-92D0-4E6F-83DB-D4009B55C678}
AppName={#MyAppName}
AppVerName={#MyAppVerName}
AppPublisher={#MyAppPublisher}
DefaultDirName={pf}\Company Programs
DisableDirPage=yes
DefaultGroupName=Company Programs
DisableProgramGroupPage=yes
OutputBaseFilename=Setup
SetupIconFile=Company_Icon.ico
Compression=lzma
SolidCompression=true
Uninstallable=false

[Files]
; Copy all the Company Programs files shared directory
Source: O:\Company Programs\*; DestDir: {app}; Flags: ignoreversion recursesubdirs createallsubdirs uninsremovereadonly
; Copy the "FullTrust" needed for the .NET Framework
Source: NET Framework\v1.1.4322\*; DestDir: C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG; Flags: ignoreversion recursesubdirs createallsubdirs uninsremovereadonly

[Run]
; Run all our installers (network drives, printers, DBISAM, Winzip, .NET Framework and the Service Pack) from the nested /Installers directory
Filename: {src}\Installers\MapDrives.bat; WorkingDir: {src}; StatusMsg: Mapping Network Drives
Filename: {src}\Installers\MapPrinters.bat; WorkingDir: {src}; StatusMsg: Mapping Network Printers
Filename: {src}\dbisam\424b1dbisamodbcstd.exe; WorkingDir: {src}; StatusMsg: Installing DBISAM
Filename: {src}\Installers\winzip81.exe; WorkingDir: {src}; StatusMsg: Installing WinZip
Filename: {src}\Installers\dotnetfx.exe; WorkingDir: {src}; StatusMsg: Installing .NET Framework
Filename: {src}\Installers\NDP1.1sp1-KB867460-X86.exe; WorkingDir: {src}; StatusMsg: Installing .NET Framework Update

[Registry]
; Create registry keys for ODBC connections for the database
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM 4 Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: PrivateDirectory; ValueType: string; ValueData: C:\Temp
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\ODBC Data Sources; ValueType: string; ValueName: DBISAM Local Tables; ValueData: DBISAM 4 ODBC Driver; Flags: createvalueifdoesntexist uninsdeletevalue
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: Driver; ValueType: string; ValueData: C:\WINDOWS\system32\dbodbc.dll
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: ConnectionType; ValueType: string; ValueData: Local
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemoteHostName; ValueType: string; ValueData:
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemoteIPAddress; ValueType: string; ValueData: 127.0.0.1
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemotePort; ValueType: string; ValueData: 12005
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemoteService; ValueType: string; ValueData:
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemoteCompression; ValueType: string; ValueData: 0
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: UID; ValueType: string; ValueData:
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: PWD; ValueType: string; ValueData:
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: RemoteReadAhead; ValueType: string; ValueData: 50
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: MRUDatabase1; ValueType: string; ValueData: Memory
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: MRUDatabase2; ValueType: string; ValueData: C:\DB_Config\Tables
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: CatalogName; ValueType: string; ValueData: C:\DBL_Config\Tables
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: ReadOnly; ValueType: string; ValueData: False
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: LockRetryCount; ValueType: string; ValueData: 15
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: LockWaitTime; ValueType: string; ValueData: 100
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: ForceBufferFlush; ValueType: string; ValueData: False
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: StrictChangeDetection; ValueType: string; ValueData: False
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: MRUPrivateDirectory1; ValueType: string; ValueData: C:\DOCUME~1\MyUserName\LOCALS~1\Temp\
Root: HKLM; SubKey: Software\ODBC\ODBC.INI\DBISAM Local Tables; Flags: createvalueifdoesntexist uninsdeletevalue; ValueName: PrivateDirectory; ValueType: string; ValueData: C:\DOCUME~1\MyUserName\LOCALS~1\Temp\
Categories: Programming Tags:

Uninstall Events for Firefox Toolbars

August 18th, 2009 Matt J. Wilson No comments

Recently I came across the need to run a script during the request to uninstall a Firefox toolbar (specifically for metric keeping purposes).  I could find almost zero documentation on this, so I’m posting a full example here.  This is basically accomplished by registering your observer on the toolbar’s initialization and then observing for the requested action:

function InitToolbar()
{

// Register for uninstalls
myUninstallObserver.register();
}

var myUninstallObserver = {
_uninstall : false,
_tabs : null,

observe : function(subject, topic, data) {
if (topic == "em-action-requested")
{
// We flagged the extension to be uninstalled
subject.QueryInterface(Ci.nsIUpdateItem);

// The following id should match up to the id in the install.rdf file
if (subject.id == "toolbar@mattjwilson.com")
{
if (data == "item-uninstalled")
{
this._uninstall = true;
}
else
{
if (data == "item-cancel-action")
{
this._uninstall = false;
}
}
}
}
else
{
if (topic == "quit-application-granted")
{
// FireFox is shutting down
if (this._uninstall)
{
// - - - - - - - - - - - - - - - - - - - - -
//
// CODE TO RUN DURING UNINSTALLATION HERE
//
// - - - - - - - - - - - - - - - - - - - - -

}
this.unregister();
}
}
},

register : function() {
var observerService = Cc["@mozilla.org/observer-service;1"]
.getService(Ci.nsIObserverService);
observerService.addObserver(this, "em-action-requested", false);
observerService.addObserver(this, "quit-application-granted", false);
},

unregister : function() {
var observerService = Cc["@mozilla.org/observer-service;1"]
.getService(Ci.nsIObserverService);
observerService.removeObserver(this, "em-action-requested");
observerService.removeObserver(this, "quit-application-granted");
}
}

window.addEventListener("load", function() {InitToolbar()}, false);
Categories: Programming Tags: , ,

Address Pattern Regular Expression

July 14th, 2009 Matt J. Wilson No comments

So I searched the internet for quite a while to find a street address regex that actually worked (and worked well).  I didn’t manage to accomplish this, so I wrote my own.  I’m keeping the address extraction algorithms and logic private, but I figured I could at least share the first part of the Regex:

Regex addressPattern = new Regex(@"(?<city>[A-Za-z',.\s]+) (?<state>([A-Za-z]{2}|[A-Za-z]{2},))\s*(?<zip>\d{5}(-\d{4})|\d{5})");

MatchCollection matches = addressPattern.Matches(text);

for (int mc = 0; mc < matches.Count; mc++)
{
string city =  matches[mc].Groups["city"].Value;
string state =  matches[mc].Groups["state"].Value;
string zip =  matches[mc].Groups["zip"].Value;
}

[/c-sharp]

Categories: Programming Tags: ,

Mimic a Login with CURL

June 11th, 2009 Matt J. Wilson No comments

Maybe this concept is much easier for others, but I had a hard time figuring out how to properly use CURL and PHP to mimic a login.  I’ll go ahead and post the code and then describe how you can figure out exactly what you should be doing to mimic a website’s login process:

<?
$ch = curl_init();

// Let's set the URL of where we want the form to POST to
curl_setopt($ch, CURLOPT_URL, 'http://www.somewebsite.com/login');

// Set the referring page
curl_setopt ($ch, CURLOPT_REFERER, "http://www.somewebsite.com");

// Make sure we enable the POST
curl_setopt ($ch, CURLOPT_POST, 1);

// Set the parameters for the POST fields
curl_setopt ($ch, CURLOPT_POSTFIELDS, 'action=login&user=Someuser&password=Somepassword&submit=Login');

// This is the key line here.  This will mimic a cookie on our machine, but instead
// will save it to the local directory of this script in a cookie.txt file.
curl_setopt ($ch, CURLOPT_COOKIEJAR, 'cookie.txt');

// We don't want it to print out the results for this, so set
// this option to 1
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);

// Execute our login request
curl_exec ($ch);

// Now we should be able to go wherever we want since we've mimiced a login
// by using curl and a 'cookie.txt' cookie jar file
$target_url = "http://www.website.com/the_page_we_want_to_visit_after_login.php";
curl_setopt($ch, CURLOPT_URL,$target_url);
curl_exec($ch);
?>

To figure out what pages to visit and exactly what items were being posted to the above page, I used a nice plugin for Mozilla called LiveHTTPHeaders.  The easiest way to detect all of the URL’s, POST’ed variables, and any other checks that a login process does is to use this add-on to capture all of that information and then mimic it with the CURL functions above.  You should be able to login and then roam around freely with your usual credentials, thanks to our cookie.txt file, which will look something like:

# Netscape HTTP Cookie File
# http://www.netscape.com/newsref/std/cookie_spec.html
# This file was generated by libcurl! Edit at your own risk.

.somewebsite.com	TRUE	/	FALSE	0	PHPSESSID	9c78c16ef63ad3cb2cd9f1d00466c319
.somewebsite.com	TRUE	/	FALSE	1276117626	isAuthorized	Y

I hope someone else finds this useful!

Categories: Programming Tags: ,

Reading a Google Docs spreadsheet using PHP

June 2nd, 2009 Matt J. Wilson No comments

So I recently came across the need to read in a Google Docs spreadhseet via PHP, which is actually incredibly easy to do.  I opted to use CSV output and had Google Docs give me a link to use.  It’s as simple as clicking “Share”, “Start Publishing”, then changing the format from “Web-Page” to “CSV (comma-separated values)”.  The link for this example is:

http://spreadsheets.google.com/pub?key=rwY-bmP3cyAzaS5xzxv3XFg&output=csv

Now for the code; my example reads in the file and inserts into my custom ‘markers’ table, but you should be able to get the picture from this:

<?
require("includes/db_conn.php");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $username, $password);
if (!$connection) {
die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die("Can\'t use db : " . mysql_error());
}

$file = fopen("http://spreadsheets.google.com/pub?key=rwY-bmP3cyAzaS5xzxv3XFg&amp;output=csv","r");

//we don't want to get the first line
$firstLine = true;

$addresses = array();
$i = 0;

while (($arr = fgetcsv($file, 1000, ",")) !== FALSE)
{
if ($firstLine)
{
$firstLine = false;
}
else
{
$arr = str_replace("'", "\'", $arr);
$addresses[$i++] = "'" . $arr[1] . "'";

$sql = "select * from markers where address ='" . $arr[1] . "';";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 0)
{
$sql = "insert into markers (name, address, description, type) values (".
"'" . $arr[0] . "', '" . $arr[1] . "', '" . $arr[2] . "', '" . $arr[3] . "');";
mysql_query($sql);
}
else
{
$sql = "UPDATE markers SET name='" . $arr[0] . "', description = '" . $arr[2] . "', type = '" . $arr[3] . "' WHERE address='" . $arr[1] . "';";
mysql_query($sql);
}
if(mysql_error())
{
echo mysql_error() ."<br>\n";
}
}
}

// delete any addresses that existed previously, but aren't in the file now
$sql = "DELETE FROM markers WHERE address NOT IN(" . implode(",", $addresses) . ");";
mysql_query($sql);

fclose($file)
?>
Categories: Programming Tags: ,

Exposing SHDocVw

March 4th, 2009 Matt J. Wilson No comments

A WebBrowserClass is just used for viewing pages embedded inside a .NET form and that’s about it, right?  The SHDocVw namespace can actually perform a ridiculous amount of neat features and override a lot of settings for something like an internet explorer add-on: getting the selected text of the browser, rewriting some of the content page (yes, you read that correctly; you can completely change the HTML of the page, including replacing text and/or images), setting the value of a selected field to something else, etc.

Want to set the user’s currently selected INPUT field to something?  Here’s a snippet for that:

try
{
mshtml.IHTMLDocument2 doc2 = (mshtml.IHTMLDocument2)Explorer.Document;

mshtml.IHTMLElement selobj = null;
mshtml.IHTMLTxtRange range = null;

if ((doc2 == null) || (doc2.activeElement == null))
return;

selobj = doc2.activeElement as mshtml.IHTMLElement;
if (selobj == null)
return;

selobj.setAttribute("value", "I just set your value.", 0);

return;
}
catch (Exception exc)
{
}

Want to handle some events, like the mouse selection changing?  Here’s a snippet for that:

m_iEvent.onselectionchange += new mshtml.HTMLDocumentEvents2_onselectionchangeEventHandler(m_iEvent_onselectionchange);

So far, I’ve been really impressed with the amount of flexibility (whether intended or not) is readily available in this class.  If anyone is interested, I can also demonstrate similar functionality using XUL/Javascript for FireFox add-ons.

Packaging a .NET Deployment Project into a Single EXE

February 13th, 2009 Matt J. Wilson No comments

So you want to package your MSI and EXE into a single EXE for distribution?  There’s no way to do it using .NET, but Inno Setup is an excellent, free utility that will allow us to create a bootstrap utility.  You will also need to download the ISTool pack to utilize the isxdl.dll.

This tool will ensure the user has the proper .NET framework installed; if not, it will retrieve it from the Microsoft site and begin installing it for them.   It will also retrieve your EXE and MSI files from any web location you specify, allowing them to automatically download and install (or uninstall later) for the user.

Enough rambling, here’s the code to use and enjoy (please note my link to the .NET Framework and registry checking are for 2.0):

[_ISTool]
EnableISX=true

[Setup]
AppName=YourAppName
AppVerName=Your App Version 1.0.0
MinVersion=4.1,4.0
DefaultDirName={pf}\Your Default Directory
DefaultGroupName=Your Default Group Name
Compression=lzma
SolidCompression=true
OutputBaseFilename=Your Output File Name
DisableDirPage=true
DisableProgramGroupPage=true
DisableReadyPage=false
DisableReadyMemo=true

[Files]
Source: C:\Program Files\ISTool\isxdl.dll; Flags: dontcopy

[Messages]
WinVersionTooLowError=YourAppName requires Windows 2000, Windows XP or later.

[Icons]
Name: {group}\Uninstall YourAppName; Filename: {uninstallexe}

[Code]
var
dotnetRedistPath: string;
downloadNeeded: boolean;
dotNetNeeded: boolean;
memoDependenciesNeeded: string;
uninstaller: String;
ErrorCode: Integer;

procedure isxdl_AddFile(URL, Filename: PChar);
external 'isxdl_AddFile@files:isxdl.dll stdcall';
function isxdl_DownloadFiles(hWnd: Integer): Integer;
external 'isxdl_DownloadFiles@files:isxdl.dll stdcall';
function isxdl_SetOption(Option, Value: PChar): Integer;
external 'isxdl_SetOption@files:isxdl.dll stdcall';

const
dotnetRedistURL = 'http://download.microsoft.com/download/5/6/7/567758a3-759e-473e-bf8f-52154438565a/dotnetfx.exe';
const
exeURL = 'http://www.pathtoyoursite.com/setup.exe';
const
msiURL = 'http://www.pathtoyoursite.com/Installation.msi';

function InitializeSetup(): Boolean;

begin
Result := true;
dotNetNeeded := false;

if not FileExists(ExpandConstant('{tmp}\setup.exe')) or not FileExists(ExpandConstant('{tmp}\Installation.msi'))  then
begin
isxdl_AddFile(exeURL, ExpandConstant('{tmp}\setup.exe'));
isxdl_AddFile(msiURL, ExpandConstant('{tmp}\Installation.msi'));
downloadNeeded := true;
end

// See if the .NET Framework is already installed
if (not RegKeyExists(HKLM, 'Software\Microsoft\.NETFramework\policy\v2.0')) then begin
dotNetNeeded := true;
if (not IsAdminLoggedOn()) then begin
MsgBox('YourAppName needs the Microsoft .NET Framework to be installed by an Administrator', mbInformation, MB_OK);
Result := false;
end else begin
memoDependenciesNeeded := memoDependenciesNeeded + '      .NET Framework' ;
dotnetRedistPath := ExpandConstant('{src}\dotnetfx.exe');
if not FileExists(dotnetRedistPath) then begin
dotnetRedistPath := ExpandConstant('{tmp}\dotnetfx.exe');
if not FileExists(dotnetRedistPath) then begin
isxdl_AddFile(dotnetRedistURL, dotnetRedistPath);
downloadNeeded := true;
end;
end;
SetIniString('install', 'dotnetRedist', dotnetRedistPath, ExpandConstant('{tmp}\dep.ini'));
end;
end;

end;

function NextButtonClick(CurPage: Integer): Boolean;
var
hWnd: Integer;
ResultCode: Integer;

begin
Result := true;

if CurPage = wpReady then begin

hWnd := StrToInt(ExpandConstant('{wizardhwnd}'));

// don't try to init isxdl if it's not needed because it will error on < ie 3
if downloadNeeded then begin

isxdl_SetOption('label', 'Downloading YourAppName Components');
isxdl_SetOption('description', 'YourAppName needs to install some components. Please wait while Setup is downloading extra files to your computer.');
if isxdl_DownloadFiles(hWnd) = 0 then Result := false;
end;
if (Result = true) and (dotNetNeeded = true) then begin
if Exec(ExpandConstant(dotnetRedistPath), '', '', SW_SHOW, ewWaitUntilTerminated, ResultCode) then begin
// handle success if necessary; ResultCode contains the exit code
if not (ResultCode = 0) then begin
Result := false;
end;
end else begin
// handle failure if necessary; ResultCode contains the error code
Result := false;
end;
end;
FileCopy(ExpandConstant('{tmp}\Installation.msi'),  ExpandConstant('{app}\Installation.msi'), false);
FileCopy(ExpandConstant('{tmp}\setup.exe'),  ExpandConstant('{app}\setup.exe'), false);
end;
end;

function UpdateReadyMemo(Space, NewLine, MemoUserInfoInfo, MemoDirInfo, MemoTypeInfo, MemoComponentsInfo, MemoGroupInfo, MemoTasksInfo: String): String;
var
s: string;

begin
if memoDependenciesNeeded <> '' then s := s + 'Dependencies to install:' + NewLine + memoDependenciesNeeded + NewLine;
s := s + MemoDirInfo + NewLine + NewLine;

Result := s
end;

[Run]
Filename: msiexec.exe; Parameters: "/i ""{tmp}\Installation.msi "" /qb"

[UninstallRun]
Filename: msiexec.exe; Parameters: "/x ""{app}\Installation.msi "" /quiet"

Thanks to the various posts on the web that gave me the basic understanding of Inno Setup and allowed me to create this script.

Categories: Programming Tags: ,

Microsoft Excel Drivers and IMEX

February 13th, 2009 Matt J. Wilson 2 comments

For any developers that are ever forced to develop in VBA, such as myself, I take pity on you for having to deal with some of Microsoft’s idiotic software kinks.  My latest gripe?  The use of any built-in driver to query an Excel sheet.

Here’s an example of my sheet:

   NumberOne
1  123456
2  987654
3  135791
4  246802
5  503513
6  546516
7  889846
8  984658
9  Some Text Here

I tried multiple data source connections, such as these two:

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};Readonly=1;DBQ=" & SourceFile
dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

So the data I read in for my NumberOne column should be the following values: 123456, 987654, 135791, 246802, 503513, 546516, 889846, 984658, Some Text Here.

What do these actually produce: 123456, 987654, 135791, 246802, 503513, 546516, 889846, 984658, null.

Now why would a null result be produced? I specifically stated to use the IMEX parameter, but it is simply not enough to overcome the stupidity of Microsoft.  Any built-in Excel driver will query the first 8 rows of a sheet and then make a determination (without your permission or knowledge) as to what type of column it is, thereby ignoring anything that doesn’t meet this data type later in the sheet.  There are no exceptions, no warnings, and no way around it but to insert a “MICROSOFT_IS_STUPID” cell in one of these rows, that you then must explicitly ignore, to force the driver to stop it’s ridiculous assumptions and read all of your data.  Also, please note that having a header row won’t rectify the problem, you must have some intermixed data types in the first 8 rows of data.

Categories: Programming Tags: , ,