ADO Connection to Excel

[petermeachem]

I've asked this before, but didn't get very far.

I am trying to open an Excel sheet and display it in a grid and edit it.

Set cnn = New ADODB.Connection
cnn.ConnectionString = cConnectionString
cnn.Open

adoPrimaryRS.Open csql, cnn, adOpenStatic, adLockOptimistic
Set GridEX1.ADORecordset = adoPrimaryRS

If someone could enlighten me on what cConnectionString and csql should contain, I should be very pleased. All I have managed to do is to produce a variety of different error messages, none of which could be classed as helpful.



Peter Meachem
peter@accuflight.com

 

[MatsHulten]

Try these two:
=========================================================================================

Dim oRs As New ADODB.Recordset
Dim oConn As New ADODB.Connection

oConn.Open "Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\ExcelFile.xls"
oRs.Open "[Sheet1$]", oConn, adOpenStatic, adLockOptimistic
oRs.Close
oRs.Open "Select * From `Sheet1$A1:B5`", oConn, adOpenStatic, adLockOptimistic
oRs.Close

Good Luck!
-Mats Hulten

 

[petermeachem]

I get Error 3709 blah de blah, with reference to a closed or invalid connection object. When I look at

print cnn.Errors(0)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Which presumably refers to msdasql? Isn't that something to do with IIS? Peter Meachem
peter@accuflight.com

 

[MatsHulten]

Sounds to me like you have some problems with your MDAC.
Do you get it when you try to open the Connection object?

-Mats

 

[petermeachem]

I actually get the error displayed when I open the recordset, but the connection open is generating an error. Peter Meachem
peter@accuflight.com

 

[MatsHulten]

Hmmm... That error do I usually get whenever I misspell a DSN name or something like that.
Can you post your code and I'll have a look on it?

-Mats

 

[petermeachem]

Dim cnn As New ADODB.Connection
Dim adoPrimaryRS As New ADODB.Recordset

cnn.Open "Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\ExcelFile.xls"
adoPrimaryRS.Open "SELECT * FROM Sheet1$", cnn, adOpenStatic, adLockOptimistic
adoPrimaryRS.Close
adoPrimaryRS.Open "Sheet1$", cnn, adOpenStatic, adLockOptimistic
adoPrimaryRS.Close

On my NT4 pc, I get an 80004005 error on cnn.open. This pc has mdac 2.1

On my win 98 one I get 80040e21 on adoPrimaryRS.open which is odbc driver does not support the requested properties. This pc has mdac 2.5

I'll put mdac 2.6 on the win 98 one and see if it makes any odds.

I'm not terribly keen on making too many changes to my nt4 pc, it's liable to upset setups. Peter Meachem
peter@accuflight.com

 

[MatsHulten]

If you don't want to update your MDAC, check to see if you have the ODBC driver for MS Excel installed. Perhaps it will be enogh to install/update this to make it work.
How to find this though I don't know...

-Mats

Ps: My Excel-driver is v4.00.5303.01 and is contained within ODBCJT32.DLL But then again, I'm running Win2k-AS

 

[petermeachem]

I downloaded mdac 2.5 and comcheck. Comcheck seems ok about the version number. My ODBCJT32.DLL is also v4.0.5303.1
and...

I get the same error ( this on the win98 ).

This all seems a bit flaky doesn't it. The basic problem is that I don't understand what is going on, and I can't find any useful info on MS.
Peter Meachem
peter@accuflight.com

 

[petermeachem]

All well now. I had a multitude of things wrong, most of which, it turn's out I didn't tell you about as I didn't think they were important.

For the record I now have:-

Global cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Letters Direct\Letters.xls;Extended Properties=Excel 5.0;"
cnn.Open


Dim adoPrimaryRS As Recordset
Set adoPrimaryRS = New Recordset

csql ="SELECT [Sheet1$].[Surname] AS LastName,[Sheet1$].[Firstname] AS FirstName,[Sheet1$].[Title] AS Title,[Sheet1$].[address] AS Address From [Sheet1$] ORDER BY [Sheet1$].[Surname] , [Sheet1$].[Firstname]"

adoPrimaryRS.Open csql, cnn, adOpenStatic, adLockOptimistic
Set GridEX1.ADORecordset = adoPrimaryRS


The last one that got me was you have to have square brackets in From [Sheet1$].
Thanks for your help Mats

Peter Meachem
peter@accuflight.com

 

[MichaelRed]

Peter,

Glad that you finally got this solved. I am the NOT fan of using Excel in almost ANY way whatsoever. The users and purveyours of spreadsheets appear (to me) to have almost no concept of dicipline, order, organization ... & perhaps respect for the needs and efforts of others.

They (appear to me) will alter any/every spreadsheet on a momentary whim to add a what if scenario, or a new calculation. I have had this happen in industries where there was a strict set of guidelines for 'corporate' objects as well as user groups of three!

I have developed a somewhat jaundiced approach dealing with spreadsheets and always get the information into a temporary recordset, and doing extensive validation of the content before use or display in controls. I even go to the extent of checking that the number of columns are what is expected, that the number of rows is within certain boounds, Col names are same as the previous/expected values, data types within the cols are as anticipated ... and on and on ... and on.

My attitude is Shakesperian, [red]" ... the question is not wheather I'm paranoid, but wheather I'm paranoid ENOUGH! ... "[/red]

I'm not really trying to 'scare' you, just inject a degree of caution.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[petermeachem]

Absolutely agree. I've yet to see anyone do any decent programming with Excel. However, this programme needs to let users use data from a variety of sources, and Excel is widely used for inappropriate things. So long as it is provided as an option, I will be happy. Peter Meachem
peter@accuflight.com