copy rows that meet criteria from excel into access.

Godders199

Active Member
Joined
Mar 2, 2017
Messages
313
Office Version
  1. 2013
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top