determining contiguous Excel columns that make up an "option group"

[pietlinden]

I'm using Access 2002, Excel 2002

I have a series of Excel files that are in a screwy format and I
wanted to retrieve the column names from within Access using ADO
instead of using DAO (which works, sort of!) This works (so you get
an idea of what I'm trying to do...)

Here's my ugly code using DAO:

Option Compare Database
Option Explicit

Public Sub GetFieldNames(ByVal strFile)
' pass in the result of the function that gets the list of files.
'---I need to convert this to ADO and just use the Columns collection
of the catalog object.
On Error GoTo ErrHandler:

Dim appXL As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim intCounter As Integer
Dim rs As DAO.Recordset

Set appXL = New Excel.Application

'--I'm running this from a form so I get some feedback...
Forms!form1.Controls("lblFileBeingProcessed").SetFocus
Forms!form1.Controls("lblFileBeingProcessed") = strFile
Forms!form1.Repaint

'--then I used Excel so I could get the UsedRange of the file
appXL.Workbooks.Open strFile

Set rs = DBEngine(0)(0).OpenRecordset("tblExcelColumns",
dbOpenTable, dbAppendOnly)
'---OOOOkay! worksheet collections are 1-based...
Set xlSheet = appXL.Worksheets(1)

For intCounter = 1 To xlSheet.UsedRange.Columns.Count
' add the column names to the table, but skip blank columns
If Len(Trim(xlSheet.Cells(1, intCounter))) > 0 Then
rs.AddNew
'MsgBox xlSheet.Cells(1, intCounter)
rs.Fields("Filename") = strFile
rs.Fields("ColumnName") = xlSheet.Cells(1, intCounter)
rs.Fields("ColumnNumber") = intCounter
rs.Update
End If

Next intCounter

'MsgBox intCounter & " records hopefully written!"

rs.Close
Set rs = Nothing
Set xlSheet = Nothing
appXL.Workbooks.Close
appXL.Quit
Set appXL = Nothing

Exit Sub

ErrHandler:
If Err.Number = 3022 Then 'duplicate values ... just reject the
insert.
Err.Clear
Resume Next
Else
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description,
vbOKOnly + vbInformation

rs.Close
Set rs = Nothing
Set xlSheet = Nothing
appXL.Workbooks.Close
appXL.Quit
Set appXL = Nothing
End If

End Sub
Well, what I _really_ wanted to do was to be able to query the columns
in the Excel file directly, because I'm looking for instances of
columns that look like this:

Smoke Often | Smoke Sometimes | Smoke Seldom | Don't Smoke | Quit
X
X
....

I'm trying to return groups of contiguous column names that comprise a
single "question". These can be identified by the following
"features"
- each question will have the same number of "x's" as there are
records, but in different columns.
(So I guess I could get a recordcount using ADO and then read across
the columns until I've reached that number of records...)

is this possible using just ADO? If so, could someone post a small
example?


Thanks!
Pieter

 

[John Nurick]

Hi Piet,

This doesn't feel like a safe approach.

1) It would fail if there are any missing or superfluous Xs - in other
words it requires a degree of data integrity that Excel worksheets
cannot normally offer.

2) Even if data integrity is assured, it would be quite possible for
overlapping groups of columns to pass the "count of Xs = count of rows"
test.

I think it would be much better to have human intervention. E.g. give
the user a list of the column headers and let them say which belongs in
which group.

 

[pietlinden]

Thanks for the response, John. I was planning on using the code to
flag structures etc that needed to be fixed prior to import into a
final normalized table.

The basic idea was to make it easier to identify the columns etc that
needed to be fixed.

 

[John Nurick]

Piet,

For myself I'd probably use DAO because it's what I'm used to. You
mentioned using a Catalog object. AFAIK that's ADOX rather than ADO,
where you'd use Connection.OpenSchema to return a recordset containing
what you need.

Searching Google Groups for
openschema adschemacolumns excel
looks as if it will get you started.