I will be grateful for the SQL syntax to use in ADO to extract specific
columns from an Excel workbook, i need to extract say column headed "ABC"and
column headed "LMN" in a closed workbook.

Any help will be gratefully received
--
with kind regards

Spike

Something like this:


Sub ADOTest()

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set Cnn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")

Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Rs.Open "Select ABC, LMN FROM [Sheet1$]", _
Cnn, adOpenStatic, adLockOptimistic, adCmdText

Do Until Rs.EOF
Debug.Print Rs.Fields.Item("ABC"), _
Rs.Fields.Item("LMN")
Rs.MoveNext
Loop

End Sub


--
urkec


"Spike" wrote:

> I will be grateful for the SQL syntax to use in ADO to extract specific
> columns from an Excel workbook, i need to extract say column headed "ABC"and
> column headed "LMN" in a closed workbook.
>
> Any help will be gratefully received
> --
> with kind regards
>
> Spike

Thank you very much for that works fine but if the column heading is two
separate words does not like it and get an error message "syntax error,
(missing operator) in query expression 'Gross Assets"".

How do i get around this.
--
with kind regards

Spike


"urkec" wrote:

> Something like this:
>
>
> Sub ADOTest()
>
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adCmdText = &H1
>
> Set Cnn = CreateObject("ADODB.Connection")
> Set Rs = CreateObject("ADODB.Recordset")
>
> Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=C:\Test.xls;" & _
> "Extended Properties=""Excel 8.0;HDR=Yes;"";"
>
> Rs.Open "Select ABC, LMN FROM [Sheet1$]", _
> Cnn, adOpenStatic, adLockOptimistic, adCmdText
>
> Do Until Rs.EOF
> Debug.Print Rs.Fields.Item("ABC"), _
> Rs.Fields.Item("LMN")
> Rs.MoveNext
> Loop
>
> End Sub
>
>
> --
> urkec
>
>
> "Spike" wrote:
>
> > I will be grateful for the SQL syntax to use in ADO to extract specific
> > columns from an Excel workbook, i need to extract say column headed "ABC"and
> > column headed "LMN" in a closed workbook.
> >
> > Any help will be gratefully received
> > --
> > with kind regards
> >
> > Spike

Further to my earlier post i have sorted it by putting the headings in square
bracketd.

Many thanks for your help works a dream
--
with kind regards

Spike


"Spike" wrote:

> Thank you very much for that works fine but if the column heading is two
> separate words does not like it and get an error message "syntax error,
> (missing operator) in query expression 'Gross Assets"".
>
> How do i get around this.
> --
> with kind regards
>
> Spike
>
>
> "urkec" wrote:
>
> > Something like this:
> >
> >
> > Sub ADOTest()
> >
> > Const adOpenStatic = 3
> > Const adLockOptimistic = 3
> > Const adCmdText = &H1
> >
> > Set Cnn = CreateObject("ADODB.Connection")
> > Set Rs = CreateObject("ADODB.Recordset")
> >
> > Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=C:\Test.xls;" & _
> > "Extended Properties=""Excel 8.0;HDR=Yes;"";"
> >
> > Rs.Open "Select ABC, LMN FROM [Sheet1$]", _
> > Cnn, adOpenStatic, adLockOptimistic, adCmdText
> >
> > Do Until Rs.EOF
> > Debug.Print Rs.Fields.Item("ABC"), _
> > Rs.Fields.Item("LMN")
> > Rs.MoveNext
> > Loop
> >
> > End Sub
> >
> >
> > --
> > urkec
> >
> >
> > "Spike" wrote:
> >
> > > I will be grateful for the SQL syntax to use in ADO to extract specific
> > > columns from an Excel workbook, i need to extract say column headed "ABC"and
> > > column headed "LMN" in a closed workbook.
> > >
> > > Any help will be gratefully received
> > > --
> > > with kind regards
> > >
> > > Spike