[AccessD] Accessing individual cell information from excel withan ADO connection

You need to use the connections connection string, not the connection
itself


	rs.Open "SELECT MyCellTitle From MAY03", cnn.connectionstring,
adOpenDynamic, adLockOptimistic

I think :o)

mark


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Powell, David
(BBM)
Sent: 07 May 2003 09:00
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Accessing individual cell information from excel
withan ADO connection


Thanks Drew for the VERY quick reply.  Just one problem though.  The SQL
part doesn't work.  This is how I have set up my code thus far:

  Dim cnn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
    .Open "C:\MyPath\MyXLSpreadsheet.xls"
  End With
  Set rs = New ADODB.Recordset
  rs.Open "SELECT MyCellTitle From MAY03", cnn, adOpenDynamic,
adLockOptimistic

If I add a $ to the end of MAY03 in the select statement as suggested, I
get an error "syntax error in FROM clause"  If I remove the $ I get an
error "The Microsoft Jet database engine could not find the object
'MAY03'

I have successfully connected to another spreadsheet and succeeded, but
created a new spreadsheet from scratch and have the same results as the
first one.  Any idea why I can't connect to this spreadsheet?


Regards

David


-----Original Message-----
From: Drew Wutka [mailto:DWUTKA at marlow.com] 
Sent: Wednesday, 7 May 2003 1:31 PM
To: 'AccessD '
Subject: RE: [AccessD] Accessing individual cell information from excel
with an ADO connection


Select `MyCellsTitle`
>From `Sheet1$`

That SQL will open a recordset with only one row.  (Of course the row
needs to have a header titled MyCellsTitle (whatever is in the first
row).

To open a ADO connection object for an excel spreadsheet:

With cnnExcel
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
End With
cnnExcel.Open strPath

-----Original Message-----
From: Powell, David (BBM)
To: AccessD
Sent: 5/7/03 12:10 AM
Subject: [AccessD]  Accessing individual cell information from excel
with an ADO connection

Hi, 

I am trying to collate relevant data which is entered into a vast number
of excel spreadsheets into one centralised access database.  Can someone
please point me in  the right direction on how to reference a cell to
copy to a table field.


TIA 

David 




NOTICE - This message and any attached files may contain information
that is confidential and/or subject of legal privilege 
intended only for use by the intended recipient. If you are not the
intended recipient or the person responsible for delivering 
the message to the intended recipient, be advised that you have received
this message in error and that any dissemination, 
copying or use of this message or attachment is strictly forbidden, as
is the disclosure of the information therein. 
If you have received this message in error please notify the sender
immediately and delete the message

Worsley Alumina Pty Ltd ABN 58 008 905 155 is the manager of the Worsley
Joint Venture - Bauxite/Alumina Operation. Liability and responsibility
of the Joint Venturers is several in accordance with the following
schedule of participating interests: Billiton Aluminium (RAA) Pty Ltd 56
percent, Billiton Aluminium
(Worsley) Pty Ltd 30 percent, 
Kobe Alumina Associates (Australia) Pty Limited 10 percent, Nissho Iwai
Alumina Pty Limited 4 percent.

 <<Powell, David (BBM).vcf>>  <<ATT04374.txt>> 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003