Can Access read the contents of an Excel file with

Question

Is it possible to read the header row of an Excel workbook to determine if certain fields exist before importing to a table? I would like there to be a notification if those certain fields are missing before the import happens.

My Access database requires users to import data from Excel files that are generated by a different system where users can choose the fields to export. Sometimes users accidentally fail to select certain fields that are required for my database to work, which causes errors, and then I get emails. There is currently no error on import if fields are missing, which is fine because not all fields are required, just certain ones.

For reference, this is the code I'm using to import right now. It runs fine and does what it's supposed to:

Private Sub ImportEDAS()
On Error GoTo SubError

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    If IsFile(Me.ImportFolder & "\perselection.xlsx") = True Then
        DoCmd.OpenQuery "qryClearEDAS"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblEDAS", Me.ImportFolder & "\perselection.xlsx", True
        MsgBox DCount("*", "tblEDAS") & "Records Imported", vbInformation + vbOKOnly, "EDAS Import Status"
    Else
        MsgBox "Could not find perselection.xlsx in the import folder.", vbCritical + vbOKOnly, "EDAS Import Status"
    End If

SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
SubError:
    MsgBox "Error Number: " & Err.Number & "- " & Err.Description, vbCritical + vbOKOnly, "ImportEdas Sub Error"
    Resume SubExit
End Sub

Answer 1

You can create a query to lookup Worksheet-Data without opening or linking.

SELECT *
FROM [Sheet1$] IN 'C:\Path\to\File\ExcelFile.xlsx'[Excel 12.0;HDR=No;IMEX=0;];

Can be used with DAO and similar with OLEDB.

Answer 2

Use

DoCmd.TransferSpreadsheet acLink, ..

and the data will be linked. The linked table you can open as a recordset and read the field names to verify these. If OK, run an append query to import the data.

Answer 3

Whilst it is certainly possible to use VBA to read the column headings within an Excel worksheet by interfacing with Excel using the ActiveX object model, an easier approach might be the following:

Import the Excel spreadsheet into a temporary table in MS Access and then use VBA to iterate over the fields in the newly created table, testing for the presence or absence of the set of fields that you require for your queries.

To validate the set of fields present in the temporary table, you could use a function such as the following:

Function ValidateFields(strTbl As String, arrReq As Variant) As Boolean
    Dim fld
    Dim fldTmp As Field
    On Error GoTo err
    For Each fld In arrReq
        Set fldTmp = CurrentDb.TableDefs(strTbl).Fields(fld)
    Next fld
    ValidateFields = True
err:
    Exit Function
End Function

Supplied with the name of the temporary table, and an array of the fields that you require to be present in such table, the above function will return True if all fields in the array are present, else False if any field in the array is missing from the table, e.g.:

?ValidateFields("TempTable", Array("Field1", "Field2", "Field3"))
False

If a number of required fields are absent, you can then notify the user accordingly; else, if all required fields are present, you can then insert the data from the required fields into the table used by your queries using a simple Append query.