I'm using the "Microsoft.Jet.OLEDB.4.0" provider for a basic connection in some VBA code, and the code works everywhere except on windows 7 64-bit operating systems running a 64-bit installation of Microsoft Office Excel 2010.
Literally every other combination of XP 32 or 64, Vista 32 or 64, and 7 32, with Excel 2003, 2007, or 2010 installations has no problem running this code, but on the above described system, it results in an error about a "Missing Provider" and I can't create the connection string.
With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeRead
.ConnectionString = "Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
.Open
End With
I've done a ton of research, but from what I can tell, the operating system is supposed to come with a full set of providers, including the 32-bit version of the Jet Provider (no 64 bit version exists), and Excel should have no problem using it for the connection. Any ideas?
Best Answer
I don't know if this will be helpful to people outside my specific example. I was using the provider to perform a query on a CSV. For instance:
Here, the source file has been modified so that all of the columns are named Cn by their headers:
So in my case, I have a file that looks like this:
Normally, using the Jet OLEDB provider, the above query string can be used to read the contents of the file into a cell:
But in the above code "ProviderError" will get triggered on 64 bit machines because there is no Jet provider available. My workaround was as follows. I actually load the file into excel, and parse the query string myself. I break the query string by comma delimiter so that each section of the query string becomes the formula for a new cell. To create the formula, I simply prepend an
=
sign and replace the string "Cn" with a reference to the source column. This way, complex queries like(C2+C4*10000000)
still get evaluated. I then copy down the formula according to the length of the source data, and then overwrite the formulas with hard coded values. The end result is identical to doing a complex Jet OLEDB query, albeit slightly slower. Code is below.The above solution assumes a query with 3 columns, but could easily be adjusted to take a any query, use split to get as many columns as there are, and dynamically redim pos() and col() arrays.