ADO SQL syntax for extracting specific columns from Excel to Excel

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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

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