Automate communication between Access and Excel
Posted on 2004-08-31
I need help with automating communication between Excel and Access. I have a number of Excel spreadsheets, in different workbooks, each containing hundreds of records that have been filtered down to disply only 5 to 10 records. What I need the macro to do is update specific records in an Access table as follows:
- update the 'Select' field (which is a Yes/No Check Box field) to No for any records that may have been previously set to Yes;
- the 'Select' field is in 'tblSales' which is always in the following location: Z:\Data\OPTC_StatData.MDB
- 'tblSales' has approximately 50,000 records, and I would like to set the value of the 'Select' field to Yes for only those records that are presently *displayed* on the active worksheet and identified by the 'Sale#'. The 'Sale#' column in my worksheet is always in Column B, although it is hidden. There may be hundreds of rows, but only 10 or so rows are visible. I hope this makes sense.
For example, I would like to update to Yes, only the records in tblSales that are shown below: 21050, 19935, 20631, etc.
Sale# SaleType ID
21050 Industrial Pre-2002 Nov_00_96_CROCKFORD_400000
19935 Industrial Pre-2002 Sep_00_103_MANVILLE_502294
20631 Industrial Pre-2002 Oct_00_36_CRANFIELD_575000
19519 Industrial Pre-2002 Aug_00_169_BARTLEY_618152
26357 Industrial Pre-2002 Jun_01_1744_MIDLAND_620000
38029 Industrial 2002-2005 Oct_02_330_NANTUCKET_660000
35613 Industrial 2002-2005 Jul_02_199_ASHTONBEE_675000
22195 Industrial Pre-2002 Jan_01_60_MODERN_750000
28414 Industrial Pre-2002 Oct_01_403-405_MIDWEST_775000
26625 Industrial Pre-2002 Jul_01_84-86_CROCKFORD_858700
Thanks for any suggestions you can offer.