How do u extract a specific column from an excel worksheet??

[Painkiller]

Hi all

I was wondering if someone could help me with this:
I'm trying to open an excel file in a VB program, and I don't know how to open a specific column from the worksheet in a recordset! I'm using the following code:

Set cnExcelConnection = New ADODB.Connection

With cnExcelConnection
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "Datasource=C:\ProgramFiles\Microsoft Visual Studio\VB98\autoprijzen_mei01.xls;Extended Properties=Excel 8.0;"
.Open
End With

Set rsToplease = New ADODB.Recordset

'open recordset
rsToplease.Open "select * from [toplease$]", cnExcelConnection, adOpenDynamic, adLockOptimistic

Now I'm trying to replace the '*' symbol in the SQL statement with a specific column form the worksheet, but using names such as 'A' or 'B' doesn't work. Can anybody help me out?

 

[MatsHulten]

Try something like this:

Select A1:B8 From...
Select A* From...
Select [DefinedArea$] From...

Haven't tried it though...

Good Luck!
-Mats Hulten

 

[Painkiller]

MatsHulten thanks for yor reply

I've tried it all. It doesn't work though. Still looking for an answer. Thanks anyway.

Painkiller

 

[MatsHulten]

It was a hipshot, I admit. Still check out the ADO connection to excel thread in VB - Databases forum, it may give you something to start with.

Good Luck
-Mats Hulten

 

[sdh]

if you set a sheet up in excel called table1 and give it
column headings in row 1 and fill the data beneath them using microsoft query you can select the column with an sql statement based on the column heading so can you not set your sheet up with a column heading and try selecting on that


ie

date time
12/03 11:00
12/03 12:00
13/03 13:00


select date from table1 where date=#12/03#

:

 

[Painkiller]

thanks sdh and Mats,

The program is up and running. Thanks again.

Painkiller