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

David,

Wrap the sheet name in square brackets ([ ]) and you probably will have to suffix the "$" as well.  I tried it without these and got the same error.  Added them and I got a record count back from my Excel sheet.

See M$KB 278973 for more http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

HTH,

Jim DeMarco
Director of Product Development
HealthSource/Hudson Health Plan


-----Original Message-----
From: Powell, David (BBM) [mailto:david.powell at bbm.wapl.com.au]
Sent: Wednesday, May 07, 2003 4:00 AM
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


***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************