loop through recordset and compare against worksheet range

[trevor40]

Hi, all I'm trying to create a loop that will update a worksheet if an employee is not listed,
I am currently populating the worksheet from access vba, listing names as required, I now have to check if an employee in not listed on the worksheet to add it to the next row. the code below is what I have been trying so far but it still wont work. Any ideas ?

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Casual employees")
    With rs
        .MoveFirst
        Do While Not .EOF
            fn = rs.Fields("First Name")
            Ln = rs.Fields("Last Name")
            bc = rs.Fields("code #")
            es = rs.Fields("Employment Type")
            For i = 8 To Mid(last_cell1, 4, 3)
                If bc = Excel_Workbook.Worksheets("Summary Sheet").Range("A8:A" & Mid(last_cell1, 4, 3)).Value Then
                    GoTo loop1
                Else
                    Excel_Workbook.Worksheets("Summary Sheet").Range("A" & Mid(last_cell1, 4, 3) + 1).Value = bc
                    Excel_Workbook.Worksheets("Summary Sheet").Range("B" & Mid(last_cell1, 4, 3) + 1).Value = Ln & ", " & fn
                    Excel_Workbook.Worksheets("Summary Sheet").Range("C" & Mid(last_cell1, 4, 3) + 1).Value = es
                    Excel_Workbook.Worksheets("Summary Sheet").Range("J" & Mid(last_cell1, 4, 3) + 1).Formula = "=SUM($D" & Mid(last_cell1, 4, 3) + 1 & ":$I" & Mid(last_cell1, 4, 3) + 1 & ")"
                    last_cell1 = Excel_Workbook.Worksheets("Summary Sheet").Cells.SpecialCells(xlCellTypeLastCell).Address
                End If
loop1:
            Next i
            .MoveNext
        Loop
    End With

[rpeare]

Assuming the worksheet and the table are sorted differently what you would have to do is cycle through your entire excel dataset to find out if the employee exists. I don't know the format of your excel file but it may be easier/quicker to link your existing excel spreadsheet temporarily into access then creating a query comparing the employee numbers (unique key I would guess) and appending 'new' items (ones that exist in your employee table but not on your spreadsheet. Then export those items only.

[trevor40]

I'm sorry, I forgot to mark this tread Solved.