Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL syntax for extracting specific columns from Excel to Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL syntax for extracting specific columns from Excel to Excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL syntax for extracting specific columns from Excel to E
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting specific text from another cell. | Excel Discussion (Misc queries) | |||
Importing data from access to excel to a specific columns | Links and Linking in Excel | |||
extracting specific rows | Excel Discussion (Misc queries) | |||
extracting specific rows | Excel Discussion (Misc queries) | |||
I need help -extracting specific data | Excel Programming |