Access VBA: Delete Excel rows where cell value matches value in Access table

Question

I have an Access VBA script that

• Loops through a list of Excel spreadsheets in a directory
• Loops through each Sheet of each spreadsheet
• Imports the data from the sheet to an Access table

I would like to add one more step:

• Compare the Excel values in column Field1 to a list of values in Access table Exclude, and delete the entire row in Excel if a match is found.

Here's the simplified code, with a note indicating where the new code should go:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM FileList")
rs.MoveFirst

Dim appExcel As Excel.Application
Set appExcel = New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Do Until rs.EOF = True
    strFilePath = rs!FilePath
    Set objWorkbook = appExcel.Workbooks.Open(strFilePath)
    For Each objSheet In objWorkbook.Sheets
        objSheet.Select
        strQuery = 
           "INSERT INTO tblImport (Field1) 
            SELECT [Field1] 
            FROM [Excel 8.0;HDR=YES;DATABASE=" & strFilePath & "].[" & objSheet.Name & strRange & "]"
        CurrentDb.Execute strQuery, dbFailOnError
        ***Add Code Here to Delete Rows in Excel where value found in Exclusion table***
        Next objSheet
    Set objSheet = Nothing
    objWorkbook.SaveAs FileName:=strFilePath, FileFormat:=xlExcel8
    objWorkbook.Close SaveChanges:=False
    Set objWorkbook = Nothing
    rs.MoveNext
    Loop

I'm sure I'll need to Dim another RecordSet:

Dim rsExclude AS Recordset
Set rsExclude = CurrentDb.OpenRecordset("SELECT * FROM Exclude")

Then what? Or perhaps someone can recommend an alternate approach?

Answer 1

Okay, I came up with a solution. I'm not convince it's "optimal" (I was hoping for a Set-Based solution, instead of this iterative solution), but it works plenty fast. Basically, I added a Do-While loop that does the following:

• Iterate through each row number in the range
• DLookup the value against the Access table field
• If no match, iterate the counter
• If match, delete row, and decrement the total row count

Added code below:

Do While intRow <= strLastRow
    If IsNull(DLookup("Field1", "Exclude", "Field1 = '" & objSheet.Range("A" & intRow).Value & "'")) Then
        intRow = intRow + 1 'No match, move to next row
    Else
        objSheet.Rows(intRow).Delete 'Match found, delete row
        strLastRow = strLastRow - 1 'Decrease the number of rows in range
    End If
    Loop

Answer 2

An alternative approach is to create a Delete query in Access, using the Exclude table as your criteria. Then execute the query from your code after you have imported ALL of your spreadsheets (no reason to run it after each spreadsheet and slow up your macro).

Or just modify your Insert query to do it for you...

"INSERT INTO tblImport (Field1) 
SELECT [Field1] 
FROM [Excel 8.0;HDR=YES;DATABASE=" & strFilePath & "].[" & objSheet.Name & strRange & "]
WHERE [Field1] NOT IN (SELECT [yourFieldInExclusion] FROM Exclusion)"