How to read in an excel file to MS Access database and run data validations against it using VBA?

Question

I've been reading about many methods that could potentially do this but nothing seems definitive or completely applicative to my situation.

What I want to do is read in an excel file from my desktop (within MS Access VBA) and then while reading it in I want to run a series of data validations against it. Such as check if the cell at B16 is a certain value or if it is a certain string. I need to be able to loop through rows while checking values and summing values. Think of an excel file with a lot of accounting terms and numbers. How would I go about validating that data?

I am currently able to read in excel files and add them to access db tables using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MyTbl", _
            Me.txtFileName, True, "Sheet1!"

I've also read about using recordsets, using ActiveX Data Objects, and using DoCmd.TransferDatabase acLink.

Import data from Excel into MS Access

Best way to read an Excel file into an Access database

Answer 1

You say "and then while reading it in". That's not possible to do while DoCmd.TransferSpreadsheet is executing.

But you can loop through all rows using Recordset after you have read it in (MyTbl is assumed to be name of table).

Below code loops through each row, column by column:

    Dim rs As Recordset
    Dim f As Field
    
    Set rs = CurrentDb.OpenRecordset("sheet1")
    While Not rs.EOF
        For Each f In rs.Fields
            Select Case f.Value
            Case "1234"
                ' do something here when value of column = "1234"
            Case "HelloWorld"
                ' do something here when value of column = "HelloWorld"
            Case Else
                ' do something here when for all other values
                ' for example you could perform a delete here:  rs.Delete
            End Select
        Next
        rs.MoveNext
    Wend
    Set rs = Nothing

The For Each loop basically lets you iterate through all "columns" of the current "row" of your table.

You reference the value by using f.value above...

The Select Case statements allow to pick specific values. What you do after is up to you to code!