Export from Excel to Access via VBA based on cell Value

[NewPadawan]

Hello All,

I'm currently using Office 16 on Windows platform, and am looking to export data from an Excel sheet in one of my workbooks to MS Access. I've got the basic outline down, and am able to send data from Excel to Access, however, I am looking to only send rows based on a cell value. In column W lies to variables: "Done", "Not Done". I would like to export the row data ONLY if column W contains "Done". Would anyone be able to point me in the right direction on how/where to include that parameter? I've attached a sample workbook and will post the code below:

Sub TransfertoAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ace.oledb.12.0; " & _
"Data Source= C:\Users\Jennifer\Documents\InventoryControl.accdb"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "CycleCountResearchTEST", cn, adOpenKeyset, adLockOptimistic, adCmdTable

' all records in a table
r = 5 ' the start row in the worksheet

' repeat until first empty cell in column A
With rs

.AddNew ' create a new record
' add values to each field in the record
.Fields("Count ID") = Range("A" & r).Value
.Fields("Aisle") = Range("B" & r).Value
.Fields("Bay") = Range("C" & r).Value
.Fields("Level") = Range("D" & r).Value
.Fields("Case ID") = Range("E" & r).Value
.Fields("PartCode") = Range("F" & r).Value
.Fields("Description") = Range("G" & r).Value
.Fields("Asset Code") = Range("H" & r).Value
.Fields("Case Good?") = Range("I" & r).Value
.Fields("Sealed?") = Range("J" & r).Value
.Fields("Counter Notes") = Range("K" & r).Value
.Fields("System QTY") = Range("L" & r).Value
.Fields("Counted QTY") = Range("M" & r).Value
.Fields("Value") = Range("N" & r).Value
.Fields("Cycle Counter") = Range("O" & r).Value
.Fields("Over/Short") = Range("P" & r).Value
.Fields("QTY Adjusted") = Range("Q" & r).Value
.Fields("Extended Value") = Range("R" & r).Value
.Fields("Date of Last Transaction") = Range("S" & r).Value
.Fields("Root Cause") = Range("T" & r).Value
.Fields("Researcher Notes") = Range("U" & r).Value
.Fields("Fixed?") = Range("V" & r).Value
.Fields("Done?") = Range("W" & r).Value
.Fields("Researcher") = Range("X" & r).Value
.Fields("Error User ID") = Range("Y" & r).Value
.Fields("Date and Time Counted") = Range("Z" & r).Value
.Fields("Research File Name") = Range("AA" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

CycleCountResearch1 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2								Name:
3	Count ID		Location		Case	Part	Description	Asset Code	Case Good?	Sealed?	Counter Notes?	Qty in Sys	Counted Qty	Value		O/S	Qty Adjusted	Extended Value	Date of Last Transaction	Root Cause	Notes	Fixed?	Done?	Name	User ID if Error	Date And Time Counted
5	1.1012E+11	62	175	F	VZC04486365	SLS-BR0497EAEX	AWS + PCS DUAL BAND RU (4T4R, 320W)		NIL			5		$ 7,975.00	SHEEM	Short	5	$ (39,875.00)	1/31/2021	Unpick transaction error	Sending to MS Access	Yes	Done		12/1/2021 9:00	11/2/2021 6:57	CycleCountResearch1 - Copy.xlsm
6	1.1012E+11	62	189	A	VZC04694776	SLS-BR0497EAEX	AWS + PCS DUAL BAND RU (4T4R, 320W)		Match	Y	SYS 5, +3	5	8	$ 7,975.00	SHEEM	Over	32	$ 255,200.00	2/14/2021	Picked Wrong Qty (Too many/Too few)	Sending to MS Access	Yes	Done		12/1/2021 9:00	11/2/2021 7:02	CycleCountResearch1 - Copy.xlsm
7	1.1012E+11	65	157	A	VZC04541721	KRK10101/11	SM6701 28GHZ AC HW	EA2A158127	NIL			1		$ 6,092.19	christian	Short	15	$ (91,382.85)	11/25/2021	Picked from Wrong Location	Sending to MS Access	Yes	Done		12/1/2021 9:00	11/2/2021 7:01	CycleCountResearch1 - Copy.xlsm
8	1.1012E+11	66	204	A	VZC04392020	NW-LSRUCV-02VZ	BRACKET ASSEMBLY-700+850MHZ RU COVER		NIL	N		2	0	$ 107.00	ANDREW	Short	4	$ (428.00)	12/1/2021	Placed in OS&D In Error	Sending to MS Access	Yes	Done		12/1/2021 9:00	11/2/2021 10:51	CycleCountResearch1 - Copy.xlsm
9	1.1012E+11	66	198	A	VZC04097634	NW-LSRUFI-02VZ	700+850MHZ/PCS+AWS RU BRACKET ASSEMBLY-POLE MOUNTING		Match	N	SYS 12	12	16	$ 100.00	ANDREW	Over		$ -			Testing the longrange field		Not Done			11/2/2021 10:47	CycleCountResearch1 - Copy.xlsm
10	1.1012E+11	66	200	A	VZC04099455	NW-LSRUFI-02VZ	700+850MHZ/PCS+AWS RU BRACKET ASSEMBLY-POLE MOUNTING		Match	N	SYS 36	36	26	$ 100.00	ANDREW	Short		$ -			Testing the longrange field		Not Done			11/2/2021 10:51	CycleCountResearch1 - Copy.xlsm
11	1.1012E+11	66	204	A	VZC04392020	NW-LSRUCV-02VZ	BRACKET ASSEMBLY-700+850MHZ RU COVER		NIL	N		2	0	$ 107.00	ANDREW	Short		$ -			Testing the longrange field		Not Done			11/2/2021 10:51	CycleCountResearch1 - Copy.xlsm
12	1.1022E+11	55	087	B	VZC04321924	NW-LSRUPC-02VZ	CABLE ASSEMBLY-DC POWER, (15M)		NIL	N		1	0	$ 261.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:13	CycleCountResearch1 - Copy.xlsm
13	1.1022E+11	55	087	B	VZC04321924	SLS-BB1150EHEX	CPRI OPTIC MODULE (20KM,9.8GBPS, DUPLEX)		NIL	N		2	0	$ 114.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:13	CycleCountResearch1 - Copy.xlsm
14	1.1022E+11	55	087	B	VZC04321925	SLS-BB1150EHEX	CPRI OPTIC MODULE (20KM,9.8GBPS, DUPLEX)		NIL	N		2	0	$ 114.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:15	CycleCountResearch1 - Copy.xlsm
15	1.1022E+11	55	087	B	VZC04321926	NW-LSRUCV-02VZ	BRACKET ASSEMBLY-700+850MHZ RU COVER		NIL	N		1	0	$ 107.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:16	CycleCountResearch1 - Copy.xlsm
16	1.1022E+11	55	087	B	VZC04321926	SLS-BB1150EHEX	CPRI OPTIC MODULE (20KM,9.8GBPS, DUPLEX)		NIL	N		2	0	$ 114.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:16	CycleCountResearch1 - Copy.xlsm
17	1.1022E+11	55	090	C		NW-LSRUCP-01VZ	CABLE ASSEMBLY-OPTIC SINGLE OUTDOOR CABLE (10M)		NIL	N		17	0	$ 60.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:27	CycleCountResearch1 - Copy.xlsm
18	1.1022E+11	55	092	E	VZC04055409	SLS-BR0497EAEX	AWS + PCS DUAL BAND RU (4T4R, 320W)		Match	N	SYS 5	5	2	$ 7,975.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:27	CycleCountResearch1 - Copy.xlsm
19	1.1022E+11	55	096	A	VZC04472002	NW-FSPOLEH35VZ	MOUNTING BRACKET (POLE MOUNT, 35-60KG MMU)		NIL	N		3	0	$ 370.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:31	CycleCountResearch1 - Copy.xlsm
20	1.1022E+11	55	097	A	VZC04479127	NW-LSRUFI-02VZ	700+850MHZ/PCS+AWS RU BRACKET ASSEMBLY-POLE MOUNTING		NIL	N		19	0	$ 100.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:32	CycleCountResearch1 - Copy.xlsm
21	1.1022E+11	55	103	F	VZC03798066	SLS-BR0497EAEX	AWS + PCS DUAL BAND RU (4T4R, 320W)		Match	N	SYS 5	5	2	$ 7,975.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:35	CycleCountResearch1 - Copy.xlsm
22	1.1022E+11	55	106	A	VZC01595850	109142980	QS873A THERMAL PROBE		NIL	N		2	0	$ 10.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:42	CycleCountResearch1 - Copy.xlsm
23	1.1022E+11	55	106	A	VZC01595850	150049195	1800A/-48V NEM/ 52 POSITION CB/26 SELECT/6 URS/7FT RACK		NIL	N		1	0	$ 5,100.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:42	CycleCountResearch1 - Copy.xlsm
24	1.1022E+11	55	106	A	VZC01595850	407998277	CIRCUIT BREAKER, 100A, 150VDC, 600VAC		NIL	N		8	0	$ 24.00	ANDREW	Short		$ -					Not Done			11/2/2021 11:42	CycleCountResearch1 - Copy.xlsm
CycleCountResearch

Cell Formulas
Range	Formula
W5:W24	W5	=IF(P5="Good","Done",IF(ISBLANK(T5),"Not Done",IF(ISBLANK(Q5),"Not Done",IF(ISBLANK(S5),"Not Done",IF(V5="Yes","Done","Not Done")))))
X5:X24	X5	=IF((ISBLANK(CycleCountResearch!$K$2)),"",CycleCountResearch!$K$2)
R5:R24	R5	=IFERROR((IF(P5="Good",0,IF(P5="Short",(Q5*N5*-1),(Q5*N5*1)))),"")
AA5:AA24	AA5	=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R:R	Cell Value	between 1000 and 99999999	text	NO
R:R	Cell Value	between -9999999999 and -1000	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H3:J3	Any value
V3	Any value
P3	Any value
Q3:R4	Any value
H5:H24	Whole number	between 0 and 99999999
S:S	Date	between 1/1/2019 and 1/1/2055
T:T	List	='Root Cause Tree'!$B$4:$B$26
V5:V24	List	=DropDowns!$E$1:$E$3
P5:P1048576	List	=DropDowns!$A$1:$A$3
Q5:R1048576	Whole number	between -99999 and 999999