copy rows that meet criteria from excel into access.

[Godders199]

Hello, I am trying to write the code to import rows with a submission date of over 90 days ago into an existing table in access. I had VBA which will copy the whole worksheet, but now i just need to insert the rows into access which meet the about criteria.

My problem is i can't find any coding , to help with the filtering to meet criteria, before copying these rows. From what i can see it should be a docmd.applyfilter. The column heading with the date is Submissions date, and all dates are in the format DD/MM/YYYY.

Be grateful for any help.

Option Compare Database
Option Explicit

Public Sub importexcelspreadsheet()
Dim strexcelpath As String
strexcelpath = "S:\xxxx.xlsx"

docmd.applyfilter. [submissions date],date() 90
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Allocation history", strexcelpath, True, ""



Thanks

 

[Micron]

Why not just link the spreadsheet as an Access table and use an Access query to update or append to the second table? AFAIK, you cannot use that method to selectively copy rows. Unless I misunderstand your post, you would have to write complex code to (in a loop) scan each row of a spreadsheet column looking for the value that denotes the desired row, copy and append that row to a table (or recordset) then rinse and repeat until reaching the end of the spreadsheet. Not that simple.

 

[xenou]

Just to make it clear about using ApplyFilter, ApplyFilter will only work on data in Access - tables, forms, reports - but cannot be used on spreadsheets.