Excel ADO in ASP Classic - Return row 2 as header

Question

How can I use the second row in an Excel (xls & xlsx) as the column headers when reading it in through ADODB.Connection???

The Excel is from a client
The Excel cannot be altered prior to being uploaded to an ASP page
The first row is the TITLE of the Excel report (which I want to ignore)

Current solution (Code is at the end):

Client uploads Excel
Create backup copy as "original"
Open and Read first 2 rows of Excel
Overwrite row 1 with values from row 2
The first 2 rows are now identical
Close and Re-Open Excel
Select everything except values that equal name of column names -e.g. Select column1, column2, column3... from [Sheet1$] where ucase(column1) <> 'COLUMN1'
Read Excel and Print HTML

Possible ideas that I can't figure out:

Assign a "Named Range" to the second row
Delete the first row (doesn't look possible with ADO and Excel)
Change Header Row to Row 2 with HeaderRowRange.Address in VBA
Instead of creating copy in current solution, read everything from Row 2 and below, then save to a new Excel

CODE FOR CURRENT SOLUTION:

' ---------------------------------------------------------------------
' 1. Client uploads Excel
' ---------------------------------------------------------------------
xlsfolder = "D:\website\excels\"
filename = request("filename_from_asp_upload_form")
xlsfile = xlsfolder & filename
' ---------------------------------------------------------------------
' 2. Create backup copy as "original"
' ---------------------------------------------------------------------
dim fscopy
set fscopy=Server.CreateObject("Scripting.FileSystemObject")
exceltypelower = fscopy.GetExtensionName(xlsfile)
replace_text = "_BACKUP." & exceltypelower
xlsfile_copy = replace(xlsfile, ("." & exceltypelower), replace_text)
fscopy.CopyFile xlsfile,xlsfile_copy
set fscopy=nothing
' ---------------------------------------------------------------------
' EXTRA STEP - SELECT EXCEL DRIVER
' ---------------------------------------------------------------------
if  exceltype = "XLS" then
    exceldriver = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsfile & ";Extended Properties=""Excel 8.0;"""
elseif exceltype = "XLSX" then
    exceldriver = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlsfile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    exceldriver2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlsfile & ";Extended Properties=""Excel 8.0;HDR=No;"""
    '*** Second driver allows first row to be read as data and not header allowing Excel cells to be overwritten
else
    '**** default to 97-2003 format. ELSE just in case
    exceldriver = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsfile & ";Extended Properties=""Excel 8.0;"""
end if
' ---------------------------------------------------------------------
' 3. Open and Read first 2 rows of Excel
' 4. Overwrite row 1 with values from row 2
' ---------------------------------------------------------------------
' NOTE: There is another step at UPLOAD that the user flags what kind of Excel Report it is. 
'   Since there are just a few now, I have the skip_title variable hard-coded
'   IF SKIP_TITLE = "Y"... that means the column headers are on row 2
'   IF SKIP_TITLE = "N"... that means the column headers are on row 1 and this is unnecessary 
' ---------------------------------------------------------------------
if skip_title = "Y" then
    excel_query2 = "Select * from [Sheet1$] "
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3
    Set objConn = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    objConn.Open (exceldriver2)
    objRecordset.CursorLocation = adUseClient
    objRecordset.Open excel_query2 , objConn, adOpenStatic, adLockOptimistic

    counter = 1
    headcount = 0 
    colcount = objRecordSet.Fields.Count    ' How many columns there are to traverse
    p = objRecordSet.GetRows(colcount,0)    ' Write excel_query2 results for number of columns above

    objRecordSet.MoveFirst
    do until objRecordSet.EOF  or counter = 0 
        for each x in objRecordSet.Fields
            objRecordSet.Fields(headcount).Value = p(headcount, 1)  'overwrite row 1 with row 2
            headcount = headcount + 1
            next
        counter = counter - 1
        objRecordSet.MoveNext
    loop
    objRecordSet.Update
    objRecordset.Close
    objConn.Close
end if 
' ---------------------------------------------------------------------
' 5. The first 2 rows are now identical
' 6. Close and Re-Open Excel
' ---------------------------------------------------------------------
set objConnection = Server.CreateObject("ADODB.Connection") 
objConnection.Open (exceldriver)
' ---------------------------------------------------------------------
' 7. Select everything except values that equal name of column names
' -e.g. Select column1, column2, column3... from [Sheet1$] where ucase(column1) <> 'COLUMN1'
' ---------------------------------------------------------------------
excel_query = "Select column1, column2, column3... from [Sheet1$] where ucase(column1) <> 'COLUMN1' " 'example
set objRS = objConnection.execute(excel_query)
' ---------------------------------------------------------------------
' 8. Read Excel and Print HTML
' ---------------------------------------------------------------------
response.write "<table><thead><tr>"
for each x in objRS.Fields
    response.write("<th>" & ucase(x.name) & "</th>") 
next
response.write "</tr></thead>"

response.write "<tbody>"
do until objRS.EOF
    response.write "<tr>"   
    for each fld in objRS.Fields
        response.write "<td>" & fld.value & "</td>" 
    next
    response.write "</tr>"  
    objRS.MoveNext
loop
objRS.close
objConnection.Close
response.write "</tbody></table>"
' ---------------------------------------------------------------------
' Le Fin or Da End

How about running a select count(*) from the sheet to determine the number of rows, then select * ... where false to get the number of columns. Once you have those you can determine the range you need to read from, and use that explicitly (eg)select from [Sheet1$A2:E500] — Tim Williams
Finding the range is not the issue. Ultimately, it's being able to use Row 2 for column headers as you would with Row 1. Let's say column 3 on row 2 is "STATE", I would like to be able to do select * from [Sheet1$] where state='Florida' order by state instead of having to do select * from [Sheet1$] where F3='Florida' order by F3. So to avoid having to write the values from Row 2 into Row 1. — SeaBass

Tim Williams Tim Williams · Accepted Answer · 2013-09-04T22:27:22

Sub Test22()

Dim cn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim numCols As Long, numRows As Long, x As Long

    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & _
                            "\ADOXSource.xls;Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With

    'read the whole sheet
    rsT.Open "select * from [Sheet1$]", cn
    rsT.MoveLast

    'get # of rows/cols
    numCols = rsT.Fields.Count
    numRows = rsT.RecordCount

    rsT.Close

    'get data from specific range
    rsT.Open "select * from [Sheet1$A2:" & Chr(64 + numCols) & (numRows - 1) & "]", cn

    'I'm testing this in excel...
    For x = 0 To rsT.Fields.Count - 1
        Sheet1.Range("a1").Offset(0, x).Value = rsT.Fields(x).Name
    Next x
    Sheet1.Range("a2").CopyFromRecordset rsT

End Sub

Excel ADO in ASP Classic - Return row 2 as header

1 Answers