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