[AccessD] Accessing individual cell information from excel with an ADO connection

Thanks for all your input so far.  I have finally got this working and
it works well when you have columns of data with headings.  What I need
to do now is extract a cell value from a report layout which doesn't
have column headings.  Eg. Range("E21").  Any help will be greatly
appreciated.


TIA

David


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


Yes, my example had the fields and tables wrapped in `'s  (Note, that is
the character underneath the tilde on most keyboards.  Personally, when
I began using ADO with Excel, I cheated.  I used VB 6.0's Data
Environment to create a connection to an Excel spreadsheet, then I used
it's query builder to make the SQL I wanted.

I know it's cheating, but it works! <VBG>

Drew

-----Original Message-----
From: Powell, David (BBM) [mailto:david.powell at bbm.wapl.com.au]
Sent: Wednesday, May 07, 2003 3:00 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Accessing individual cell information from excel
with an 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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com