Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
Hi
I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _
"WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
That would give me the contents of columns LastName and KeyCode with filters
myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
So the WHERE clause should read something like:
WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
An xls list can be treated as just another table, and queried the same way.
For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
I appreciate the explanation however I am only looking for headers, not data
under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
If you only want the field names, the recordset has a fields collection, and
you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
As you can see from code below I get a recordset of the first row of all the
selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
Sorry, you have totally lost me now as to what you want to do.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
The supplied code yields the entire first row of selected wbooks in a
selected folder. I would like just to extract columns with specific fieldnames. It is a certainty these fieldnames will be in differing columns in different wbooks. Geoff "Bob Phillips" wrote: Sorry, you have totally lost me now as to what you want to do. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
So you want to say get the column # for a header LastName, but you don't
want to read the whole row? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... The supplied code yields the entire first row of selected wbooks in a selected folder. I would like just to extract columns with specific fieldnames. It is a certainty these fieldnames will be in differing columns in different wbooks. Geoff "Bob Phillips" wrote: Sorry, you have totally lost me now as to what you want to do. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if they are there) can be in any column order. Wbook1 keycode Source Title Wbook2 List code Honorific Forename Surname keycode etc etc Geoff "Bob Phillips" wrote: szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode & "';" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... Hi I use ADO to get the first row of data from multiple unopened wbooks. This string gets the contents of every cell in row 1 given that SourceSheet = "Sheet1" and SourceRange = "A1:IV1" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Instead of all the row how can I write a string to result in specific names eg Last Name, Keycode, Company etc? T.I.A. Geoff |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
If that is possible yes.
Geoff "Bob Phillips" wrote: So you want to say get the column # for a header LastName, but you don't want to read the whole row? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... The supplied code yields the entire first row of selected wbooks in a selected folder. I would like just to extract columns with specific fieldnames. It is a certainty these fieldnames will be in differing columns in different wbooks. Geoff "Bob Phillips" wrote: Sorry, you have totally lost me now as to what you want to do. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
I don't see how Geoff. If you don't read the whole row, how do you know how
much to read, how do you avoid missing some? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... If that is possible yes. Geoff "Bob Phillips" wrote: So you want to say get the column # for a header LastName, but you don't want to read the whole row? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... The supplied code yields the entire first row of selected wbooks in a selected folder. I would like just to extract columns with specific fieldnames. It is a certainty these fieldnames will be in differing columns in different wbooks. Geoff "Bob Phillips" wrote: Sorry, you have totally lost me now as to what you want to do. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must be exact match. myName and myCode are the variables that you wish to restrict the query to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... So the WHERE clause should read something like: WHERE fieldname = Last Name, Keycode, First Name and the other specific headers I am looking for. Is this possible working with lists in .xls wbooks? Geoff "Geoff" wrote: That would give me the contents of columns LastName and KeyCode with filters myName and myCode. I am just reading the first row of multiple Excel wbooks. This is to establish data patterns. Column names are random and specific names (if |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Select string
I saw somewhere on this site a p[ost requesting specific columns and the
answer was Select ABC, LMN FROM [Sheet1$]. I am looking to do similar but with fieldnames not specific columns. However because there doesn't seem to be an easy solution I have begun working on Find after I have pulled all the first rows of wbooks required i.e. foundFirstName = Rows(rnum + 1).Find("First Name", , , xlPart) Then do something with it if found and similar for each of the specific fieldnames I am after. This does the job BUT it is an additional task and I wanted really to do it all during the ADO pass. Geoff "Bob Phillips" wrote: I don't see how Geoff. If you don't read the whole row, how do you know how much to read, how do you avoid missing some? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... If that is possible yes. Geoff "Bob Phillips" wrote: So you want to say get the column # for a header LastName, but you don't want to read the whole row? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... The supplied code yields the entire first row of selected wbooks in a selected folder. I would like just to extract columns with specific fieldnames. It is a certainty these fieldnames will be in differing columns in different wbooks. Geoff "Bob Phillips" wrote: Sorry, you have totally lost me now as to what you want to do. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... As you can see from code below I get a recordset of the first row of all the selected wbooks in the folder. It works really well and I can get the first row from 35 wbooks in less than 3 seconds. If I open each wbook to interogate it takes more than 12 seconds. But what I would like to know is how do I amend the szSQL query to extract only the required fields. I have tried field(x).Name constructs in the query but they always error out. I have experimented with changing the asterisk in SELECT *. I have experimented with various things on the WHERE clause. But nothing has worked so far. It would be great to extract what I need in one pass rather than have to get the entire row 1 (in some case more than 100 columns wide) and do a filter on it afterwards. That is 2 stages but at the moment it's beginning to look as if I will have to do that. Geoff Courtesy of Ron de Bruin I have adapted some of the example code to my project. FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then rnum = 0 For Fnum = LBound(FName) To UBound(FName) Set destrange = sh.Cells(rnum + 1, "B") sh.Cells(rnum + 1, "A").Value = FName(Fnum) GetData FName(Fnum), "Sheet1", "A1:IU1", destrange rnum = rnum + 1 Next Else MsgBox "No files found" End If Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No"";" szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset rsData Else MsgBox "No records returned from : " & SourceFile, vbCritical End If rsData.Close Set rsData = Nothing rsCon.Close Set rsCon = Nothing End Sub "Bob Phillips" wrote: If you only want the field names, the recordset has a fields collection, and you can get at it like so For i = 0 To oRS.Fields.Count - 1 MsgBox oRS.Fields(i).Name Next i where oRS is the recordsset returned from the query. You can do a full query as originally szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] ;" to build the recordset -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff" wrote in message ... I appreciate the explanation however I am only looking for headers, not data under the headers. If I understand correctly, the clause WHERE LastName = myname would look in the column LastName for whatever value has been assigned to "myname". That is not necessary here, I only want to get specific headers. Whilst I can get the whole header row using ADO for speed, it would be even better if I can just pull the specific header names (if they exist). If the last appears strange then you only have to consider how many different ways there are of categorising the name "Bob", it can be First Name, Christian Name, ForeName, ForeName(s) for example. A shortened example of the headers extracted from individual wbooks with ADO are shown Subscription ID Delegate ID Order Type etc List code Honorific Forename etc URN HONORIFIC INITIALS Subscription ID Delegate ID Order Type keycode Source Title Order Date Name Title Christian Surname JobTitle URN HONORIFIC INITIALS FORENAME The purpose of doing a first row extraction using ADO is a fast prelim search to find wbooks with similar patterns of headers. Next stage is to sort the wbooks into groups and then present them to an existing form for data filtering. Doing it in groups, rather than singly, minimises user input to the form and potentially saves a great deal of time. Despite a lot of experiments I am unable to work out the syntax of the Query. I have modified the SELECT * part ot the query and also tried WHERE rs.fields(x).Name LIKE whatever But nothing works. I would really appreciate if it can be solved and apologise if I have misunderstood your comments. Geoff "Bob Phillips" wrote: An xls list can be treated as just another table, and queried the same way. For this, we need to have headers in row, and this is the column names for the SQL query. You can query that table, using WHERE clauses against those column names. You must specify each column to qualify (WHERE) with the value to be retrieved. To go back to my original szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] " & _ "WHERE LastName = '" & myName & "' AND KeyCode = '" & myCode We have column names (headings) of LastName and KeyCode, this must |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a string without knowing position | Excel Programming | |||
select a string of characters | Excel Worksheet Functions | |||
Word select from a string | Excel Worksheet Functions | |||
select part of a cell value string | Excel Discussion (Misc queries) | |||
Select option button when have the name as a string | Excel Programming |