ADO SQL syntax for extracting specific columns from Excel to E
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
|