Hi All,
I have an excel spreadsheet, which I want to use like a database table and access using ADO from another spreadsheet, but whenever I attempt to assign my connection string to the ActiveConnection property of my ADODB Recordset, I get the following error:
Could not find installable ISAM.
Below is the connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Le09\kpl_biskra\personal_biskra\ksmith\Reporting\Weekly\Christmas Analysis\DataFile.xls;Extended Properties=Excel 8.0;HDR=Yes;
Can anyone help
Many thanks!
Keith
Problem with Connection Strings
puffzotty
Keith, I ran into this exact issue a couple weeks ago. Unfortunately I don't remember off the top of my head what I did to fix it. Let me dig through some of my old routines and see if I can find the solution.
James S. Jan
Just to add to this, it has something to do with the fact the SQL I'm passing in my previous post is a crosstab. If I pass a simple SELECT query, like that below, the dates are fine, albeit formatted as numbers, but if I then re-format them as dates, they are all ok.
SELECT PriceBand, WeekEnding, Sum(SalesQty) FROM [Results$] WHERE Period = 'Last Year' GROUP BY PriceBand, WeekEnding
The dates for the crosstab results however come through formatted as dates, as you can see in my previous post, but with some clearly not right.
Does this ring any bells with anyone
Cheers!
Keith
hfann
Excellent! It works when I use the quotes. I use connectionstrings.com quite a bit and it quite clearly uses the quotes. I must have been half asleep!
See my connection string below:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Le09\kpl_biskra\personal_biskra\ksmith\Reporting\Weekly\Christmas Analysis\DataFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
I've bought a whopping great book on ActiveX Data Objects and have since learned that ISAM stands for Indexed Sequential Access Method or something along those lines. I can see it's going to be the most useful book I've ever bought! Anyway, I'm digressing here.
Cheers My Smyth! I love this site!
creaturita
How you doing,
This might not be the solution but your Extended Properties should be double quotes in the connection string, like this.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
You need to represent it in your code as a string like this....
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Might be the solution though.
Gerhardo
Now I feel a bit sheepish as I've gone and solved it after all that flapping about!
I simply encased the WeekEnding field in a Format function per below:
TRANSFORM Sum(SalesQty) As SalesQty SELECT PriceBand FROM [Results$] WHERE Period = 'Last Year' GROUP BY PriceBand PIVOT Format(WeekEnding,'yyyy-mm-dd')
I did try this before, but used 'dd/mm/yy', which of course didn't work.
Cheers!
Keith
zhihao
I thought I'd resurrect this thread as, although I can now successfully connect to my excel datasource and run queries, I'm not happy with the dates coming back in my recordset. They don't appear to be consistent, with some coming back ok and others with the day and month parts switched round, although they are in the correct chronological order. Below is an extract of the dates coming back, so you can see what I mean. It is a crosstab query with the dates as column headers, which are week ending Sunday:
It starts off ok, but the third date should be 06/11/05, not 11/06/05. It's not simply a case it's in american format either. That date has actually turned into 11th June 2005! The dates in my datasource are all exactly the same format and data type, so quite why it comes through like this is baffling! I've tried changing the "IMEX" part of my connection string to 0 and 2. That has no effect.
Below is the SQL I'm passing:
TRANSFORM Sum(SalesQty) As SalesQty SELECT PriceBand FROM [Results$] WHERE Period = 'This Year' GROUP BY PriceBand PIVOT WeekEnding
Does anyone who queries Excel datasources know how I can get around this problem
Many thanks!!