I Need To Export Excel Data To Access Database Daily

[NFLnut]

I posted this over in the Excel forum yesterday and received no responses (but 29 views). I'm thinking maybe this forum is more appropriate.

I am VERY new to Access, and have a spreadsheet that is generated every day that I need to selectively export data from to an Access database. My staff creates a new spreadsheet/workbook every day (which are named "072314" for today, "072414" for tomorrow, etc) and I download these from the office computer every night. On each row of the spreadsheet, a customer record is created, and some customers end up having a balance due which I need to create a database for to keep track of. Additionally, there are two (actually three, but on the sample sheet that I am sharing, I only entered two) different types of balance due that I have to keep separate so that my staff can then work with it daily in a separate database and update accordingly as the customer makes payments. I would like to add each day's balance dues to one continuously growing database.

What would be the best and easiest way to do this? I have tried some tutorials on how to export data to Access, but none of them really fit what I need to do. Plus, as I said, I am very new to Access as the last 20 years I have worked almost exclusively with Excel. (I'm not certain if I 'attached' the worksheet properly below using HTMLMaker. This is the first time I've used it.) The spreadsheets contain a lot of <acronym title="visual basic for applications">VBA</acronym> code to expand column widths when a cell is selected for my staff to read codes and long descriptions in a drop down list, so I don't want to 'break' those when scraping data to the database.

Excel 2002ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE12345Balance DueBalance Due6Bill #Last NameFirst NameMIBirthdayProc CodeProc CodeProc CodeProc CodeDiagnosisDiagnosisChargeSpcl ChargeType # 1Type # 278901SmithBob5050150501 360.08360.04 $ 200.00  - 120.00 20.00  -  - 001002SmithMary50503360.01 $ 20.00  -  - 1103JonesJim5050650505360.03 $ 30.00  -  10.00  - 1204MarksCindy50508360.05 $ 20.00  -  10.00  - 1305BelliniMartin5050150504360.07 $ 100.00  -  - 1406GrantFrances50503360.02 $ 20.00  -  - 1507 $ -  -  - 1608 $ -  -  - 1709 $ -  -  - 1810 $ -  -  - 1911 $ -  -  - 2012 $ -  -  - 2113 $ -  -  - 2214 $ -  -  - 2315 $ -  -  - 2416 $ -  -  - 2517 $ -  -  - 2618 $ -  -  - 2719 $ -  -  - 2820 $ -  -  - 2921 $ -  -  - 3022 $ -  -  - 3123 $ -  -  - 3224 $ -  -  - 3325 $ -  -  - 3426 $ -  -  - 3527 $ -  -  - 3628 $ -  -  2  - 373839[Reserved for Future Use]4041424344454647[CENTER][COLOR=#161120][B]Daysheet[/B][/COLOR][/CENTER]  Worksheet FormulasCellFormulaA9=CONCATENATE($C$1,0,1)B9=IF(U9+V9+W9=S9,"","X")A10=CONCATENATE($C$1,0,2)B10=IF(U10+V10+W10=S10,"","X")A11=CONCATENATE($C$1,0,3)B11=IF(U11+V11+W11=S11,"","X")A12=CONCATENATE($C$1,0,4)B12=IF(U12+V12+W12=S12,"","X")A13=CONCATENATE($C$1,0,5)B13=IF(U13+V13+W13=S13,"","X")A14=CONCATENATE($C$1,0,6)B14=IF(U14+V14+W14=S14,"","X")A15=CONCATENATE($C$1,0,7)B15=IF(U15+V15+W15=S15,"","X")A16=CONCATENATE($C$1,0,8)B16=IF(U16+V16+W16=S16,"","X")A17=CONCATENATE($C$1,0,9)B17=IF(U17+V17+W17=S17,"","X")A18=CONCATENATE($C$1,1,0)B18=IF(U18+V18+W18=S18,"","X")A19=CONCATENATE($C$1,1,1)B19=IF(U19+V19+W19=S19,"","X")A20=CONCATENATE($C$1,1,2)B20=IF(U20+V20+W20=S20,"","X")A21=CONCATENATE($C$1,1,3)B21=IF(U21+V21+W21=S21,"","X")A22=CONCATENATE($C$1,1,4)B22=IF(U22+V22+W22=S22,"","X")A23=CONCATENATE($C$1,1,5)B23=IF(U23+V23+W23=S23,"","X")A24=CONCATENATE($C$1,1,6)B24=IF(U24+V24+W24=S24,"","X")A25=CONCATENATE($C$1,1,7)B25=IF(U25+V25+W25=S25,"","X")A26=CONCATENATE($C$1,1,8)B26=IF(U26+V26+W26=S26,"","X")A27=CONCATENATE($C$1,1,9)B27=IF(U27+V27+W27=S27,"","X")A28=CONCATENATE($C$1,2,0)B28=IF(U28+V28+W28=S28,"","X")A29=CONCATENATE($C$1,2,1)B29=IF(U29+V29+W29=S29,"","X")A30=CONCATENATE($C$1,2,2)B30=IF(U30+V30+W30=S30,"","X")A31=CONCATENATE($C$1,2,3)B31=IF(U31+V31+W31=S31,"","X")A32=CONCATENATE($C$1,2,4)B32=IF(U32+V32+W32=S32,"","X")A33=CONCATENATE($C$1,2,5)B33=IF(U33+V33+W33=S33,"","X")A34=CONCATENATE($C$1,2,6)B34=IF(U34+V34+W34=S34,"","X")A35=CONCATENATE($C$1,2,7)B35=IF(U35+V35+W35=S35,"","X")A36=CONCATENATE($C$1,2,8)B36=IF(U36+V36+W36=S36,"","X")M9=IF(G9>0,VLOOKUP(G9,Lookups!$B$2:$G$32,3))+IF(H9>0,VLOOKUP(H9,Lookups!$B$2:$G$32,3))+IF(I9>0,VLOOKUP(I9,Lookups!$B$2:$G$32,3))+IF(J9>0,VLOOKUP(J9,Lookups!$B$2:$G$32,3))N9=IF(G9>0,VLOOKUP(G9,Lookups!B$2:$G$32,3))+IF(H9>0,VLOOKUP(H9,Lookups!$B$2:$G$32,3))+IF(I9>0,VLOOKUP(I9,Lookups!$B$2:$G$32,3))+IF(J9>0,VLOOKUP(J9,Lookups!$B$2:$G$32,3))-M9M10=IF(G10>0,VLOOKUP(G10,Lookups!$B$2:$G$32,3))+IF(H10>0,VLOOKUP(H10,Lookups!$B$2:$G$32,3))+IF(I10>0,VLOOKUP(I10,Lookups!$B$2:$G$32,3))+IF(J10>0,VLOOKUP(J10,Lookups!$B$2:$G$32,3))N10=IF(G10>0,VLOOKUP(G10,Lookups!B$2:$G$32,3))+IF(H10>0,VLOOKUP(H10,Lookups!$B$2:$G$32,3))+IF(I10>0,VLOOKUP(I10,Lookups!$B$2:$G$32,3))+IF(J10>0,VLOOKUP(J10,Lookups!$B$2:$G$32,3))-M10M11=IF(G11>0,VLOOKUP(G11,Lookups!$B$2:$G$32,3))+IF(H11>0,VLOOKUP(H11,Lookups!$B$2:$G$32,3))+IF(I11>0,VLOOKUP(I11,Lookups!$B$2:$G$32,3))+IF(J11>0,VLOOKUP(J11,Lookups!$B$2:$G$32,3))N11=IF(G11>0,VLOOKUP(G11,Lookups!B$2:$G$32,3))+IF(H11>0,VLOOKUP(H11,Lookups!$B$2:$G$32,3))+IF(I11>0,VLOOKUP(I11,Lookups!$B$2:$G$32,3))+IF(J11>0,VLOOKUP(J11,Lookups!$B$2:$G$32,3))-M11M12=IF(G12>0,VLOOKUP(G12,Lookups!$B$2:$G$32,3))+IF(H12>0,VLOOKUP(H12,Lookups!$B$2:$G$32,3))+IF(I12>0,VLOOKUP(I12,Lookups!$B$2:$G$32,3))+IF(J12>0,VLOOKUP(J12,Lookups!$B$2:$G$32,3))N12=IF(G12>0,VLOOKUP(G12,Lookups!B$2:$G$32,3))+IF(H12>0,VLOOKUP(H12,Lookups!$B$2:$G$32,3))+IF(I12>0,VLOOKUP(I12,Lookups!$B$2:$G$32,3))+IF(J12>0,VLOOKUP(J12,Lookups!$B$2:$G$32,3))-M12M13=IF(G13>0,VLOOKUP(G13,Lookups!$B$2:$G$32,3))+IF(H13>0,VLOOKUP(H13,Lookups!$B$2:$G$32,3))+IF(I13>0,VLOOKUP(I13,Lookups!$B$2:$G$32,3))+IF(J13>0,VLOOKUP(J13,Lookups!$B$2:$G$32,3))N13=IF(G13>0,VLOOKUP(G13,Lookups!B$2:$G$32,3))+IF(H13>0,VLOOKUP(H13,Lookups!$B$2:$G$32,3))+IF(I13>0,VLOOKUP(I13,Lookups!$B$2:$G$32,3))+IF(J13>0,VLOOKUP(J13,Lookups!$B$2:$G$32,3))-M13M14=IF(G14>0,VLOOKUP(G14,Lookups!$B$2:$G$32,3))+IF(H14>0,VLOOKUP(H14,Lookups!$B$2:$G$32,3))+IF(I14>0,VLOOKUP(I14,Lookups!$B$2:$G$32,3))+IF(J14>0,VLOOKUP(J14,Lookups!$B$2:$G$32,3))N14=IF(G14>0,VLOOKUP(G14,Lookups!B$2:$G$32,3))+IF(H14>0,VLOOKUP(H14,Lookups!$B$2:$G$32,3))+IF(I14>0,VLOOKUP(I14,Lookups!$B$2:$G$32,3))+IF(J14>0,VLOOKUP(J14,Lookups!$B$2:$G$32,3))-M14M15=IF(G15>0,VLOOKUP(G15,Lookups!$B$2:$G$32,3))+IF(H15>0,VLOOKUP(H15,Lookups!$B$2:$G$32,3))+IF(I15>0,VLOOKUP(I15,Lookups!$B$2:$G$32,3))+IF(J15>0,VLOOKUP(J15,Lookups!$B$2:$G$32,3))N15=IF(G15>0,VLOOKUP(G15,Lookups!B$2:$G$32,3))+IF(H15>0,VLOOKUP(H15,Lookups!$B$2:$G$32,3))+IF(I15>0,VLOOKUP(I15,Lookups!$B$2:$G$32,3))+IF(J15>0,VLOOKUP(J15,Lookups!$B$2:$G$32,3))-M15M16=IF(G16>0,VLOOKUP(G16,Lookups!$B$2:$G$32,3))+IF(H16>0,VLOOKUP(H16,Lookups!$B$2:$G$32,3))+IF(I16>0,VLOOKUP(I16,Lookups!$B$2:$G$32,3))+IF(J16>0,VLOOKUP(J16,Lookups!$B$2:$G$32,3))N16=IF(G16>0,VLOOKUP(G16,Lookups!B$2:$G$32,3))+IF(H16>0,VLOOKUP(H16,Lookups!$B$2:$G$32,3))+IF(I16>0,VLOOKUP(I16,Lookups!$B$2:$G$32,3))+IF(J16>0,VLOOKUP(J16,Lookups!$B$2:$G$32,3))-M16M17=IF(G17>0,VLOOKUP(G17,Lookups!$B$2:$G$32,3))+IF(H17>0,VLOOKUP(H17,Lookups!$B$2:$G$32,3))+IF(I17>0,VLOOKUP(I17,Lookups!$B$2:$G$32,3))+IF(J17>0,VLOOKUP(J17,Lookups!$B$2:$G$32,3))N17=IF(G17>0,VLOOKUP(G17,Lookups!B$2:$G$32,3))+IF(H17>0,VLOOKUP(H17,Lookups!$B$2:$G$32,3))+IF(I17>0,VLOOKUP(I17,Lookups!$B$2:$G$32,3))+IF(J17>0,VLOOKUP(J17,Lookups!$B$2:$G$32,3))-M17M18=IF(G18>0,VLOOKUP(G18,Lookups!$B$2:$G$32,3))+IF(H18>0,VLOOKUP(H18,Lookups!$B$2:$G$32,3))+IF(I18>0,VLOOKUP(I18,Lookups!$B$2:$G$32,3))+IF(J18>0,VLOOKUP(J18,Lookups!$B$2:$G$32,3))N18=IF(G18>0,VLOOKUP(G18,Lookups!B$2:$G$32,3))+IF(H18>0,VLOOKUP(H18,Lookups!$B$2:$G$32,3))+IF(I18>0,VLOOKUP(I18,Lookups!$B$2:$G$32,3))+IF(J18>0,VLOOKUP(J18,Lookups!$B$2:$G$32,3))-M18M19=IF(G19>0,VLOOKUP(G19,Lookups!$B$2:$G$32,3))+IF(H19>0,VLOOKUP(H19,Lookups!$B$2:$G$32,3))+IF(I19>0,VLOOKUP(I19,Lookups!$B$2:$G$32,3))+IF(J19>0,VLOOKUP(J19,Lookups!$B$2:$G$32,3))N19=IF(G19>0,VLOOKUP(G19,Lookups!B$2:$G$32,3))+IF(H19>0,VLOOKUP(H19,Lookups!$B$2:$G$32,3))+IF(I19>0,VLOOKUP(I19,Lookups!$B$2:$G$32,3))+IF(J19>0,VLOOKUP(J19,Lookups!$B$2:$G$32,3))-M19M20=IF(G20>0,VLOOKUP(G20,Lookups!$B$2:$G$32,3))+IF(H20>0,VLOOKUP(H20,Lookups!$B$2:$G$32,3))+IF(I20>0,VLOOKUP(I20,Lookups!$B$2:$G$32,3))+IF(J20>0,VLOOKUP(J20,Lookups!$B$2:$G$32,3))N20=IF(G20>0,VLOOKUP(G20,Lookups!B$2:$G$32,3))+IF(H20>0,VLOOKUP(H20,Lookups!$B$2:$G$32,3))+IF(I20>0,VLOOKUP(I20,Lookups!$B$2:$G$32,3))+IF(J20>0,VLOOKUP(J20,Lookups!$B$2:$G$32,3))-M20M21=IF(G21>0,VLOOKUP(G21,Lookups!$B$2:$G$32,3))+IF(H21>0,VLOOKUP(H21,Lookups!$B$2:$G$32,3))+IF(I21>0,VLOOKUP(I21,Lookups!$B$2:$G$32,3))+IF(J21>0,VLOOKUP(J21,Lookups!$B$2:$G$32,3))N21=IF(G21>0,VLOOKUP(G21,Lookups!B$2:$G$32,3))+IF(H21>0,VLOOKUP(H21,Lookups!$B$2:$G$32,3))+IF(I21>0,VLOOKUP(I21,Lookups!$B$2:$G$32,3))+IF(J21>0,VLOOKUP(J21,Lookups!$B$2:$G$32,3))-M21M22=IF(G22>0,VLOOKUP(G22,Lookups!$B$2:$G$32,3))+IF(H22>0,VLOOKUP(H22,Lookups!$B$2:$G$32,3))+IF(I22>0,VLOOKUP(I22,Lookups!$B$2:$G$32,3))+IF(J22>0,VLOOKUP(J22,Lookups!$B$2:$G$32,3))N22=IF(G22>0,VLOOKUP(G22,Lookups!B$2:$G$32,3))+IF(H22>0,VLOOKUP(H22,Lookups!$B$2:$G$32,3))+IF(I22>0,VLOOKUP(I22,Lookups!$B$2:$G$32,3))+IF(J22>0,VLOOKUP(J22,Lookups!$B$2:$G$32,3))-M22M23=IF(G23>0,VLOOKUP(G23,Lookups!$B$2:$G$32,3))+IF(H23>0,VLOOKUP(H23,Lookups!$B$2:$G$32,3))+IF(I23>0,VLOOKUP(I23,Lookups!$B$2:$G$32,3))+IF(J23>0,VLOOKUP(J23,Lookups!$B$2:$G$32,3))N23=IF(G23>0,VLOOKUP(G23,Lookups!B$2:$G$32,3))+IF(H23>0,VLOOKUP(H23,Lookups!$B$2:$G$32,3))+IF(I23>0,VLOOKUP(I23,Lookups!$B$2:$G$32,3))+IF(J23>0,VLOOKUP(J23,Lookups!$B$2:$G$32,3))-M23M24=IF(G24>0,VLOOKUP(G24,Lookups!$B$2:$G$32,3))+IF(H24>0,VLOOKUP(H24,Lookups!$B$2:$G$32,3))+IF(I24>0,VLOOKUP(I24,Lookups!$B$2:$G$32,3))+IF(J24>0,VLOOKUP(J24,Lookups!$B$2:$G$32,3))N24=IF(G24>0,VLOOKUP(G24,Lookups!B$2:$G$32,3))+IF(H24>0,VLOOKUP(H24,Lookups!$B$2:$G$32,3))+IF(I24>0,VLOOKUP(I24,Lookups!$B$2:$G$32,3))+IF(J24>0,VLOOKUP(J24,Lookups!$B$2:$G$32,3))-M24M25=IF(G25>0,VLOOKUP(G25,Lookups!$B$2:$G$32,3))+IF(H25>0,VLOOKUP(H25,Lookups!$B$2:$G$32,3))+IF(I25>0,VLOOKUP(I25,Lookups!$B$2:$G$32,3))+IF(J25>0,VLOOKUP(J25,Lookups!$B$2:$G$32,3))N25=IF(G25>0,VLOOKUP(G25,Lookups!B$2:$G$32,3))+IF(H25>0,VLOOKUP(H25,Lookups!$B$2:$G$32,3))+IF(I25>0,VLOOKUP(I25,Lookups!$B$2:$G$32,3))+IF(J25>0,VLOOKUP(J25,Lookups!$B$2:$G$32,3))-M25M26=IF(G26>0,VLOOKUP(G26,Lookups!$B$2:$G$32,3))+IF(H26>0,VLOOKUP(H26,Lookups!$B$2:$G$32,3))+IF(I26>0,VLOOKUP(I26,Lookups!$B$2:$G$32,3))+IF(J26>0,VLOOKUP(J26,Lookups!$B$2:$G$32,3))N26=IF(G26>0,VLOOKUP(G26,Lookups!B$2:$G$32,3))+IF(H26>0,VLOOKUP(H26,Lookups!$B$2:$G$32,3))+IF(I26>0,VLOOKUP(I26,Lookups!$B$2:$G$32,3))+IF(J26>0,VLOOKUP(J26,Lookups!$B$2:$G$32,3))-M26M27=IF(G27>0,VLOOKUP(G27,Lookups!$B$2:$G$32,3))+IF(H27>0,VLOOKUP(H27,Lookups!$B$2:$G$32,3))+IF(I27>0,VLOOKUP(I27,Lookups!$B$2:$G$32,3))+IF(J27>0,VLOOKUP(J27,Lookups!$B$2:$G$32,3))N27=IF(G27>0,VLOOKUP(G27,Lookups!B$2:$G$32,3))+IF(H27>0,VLOOKUP(H27,Lookups!$B$2:$G$32,3))+IF(I27>0,VLOOKUP(I27,Lookups!$B$2:$G$32,3))+IF(J27>0,VLOOKUP(J27,Lookups!$B$2:$G$32,3))-M27M28=IF(G28>0,VLOOKUP(G28,Lookups!$B$2:$G$32,3))+IF(H28>0,VLOOKUP(H28,Lookups!$B$2:$G$32,3))+IF(I28>0,VLOOKUP(I28,Lookups!$B$2:$G$32,3))+IF(J28>0,VLOOKUP(J28,Lookups!$B$2:$G$32,3))N28=IF(G28>0,VLOOKUP(G28,Lookups!B$2:$G$32,3))+IF(H28>0,VLOOKUP(H28,Lookups!$B$2:$G$32,3))+IF(I28>0,VLOOKUP(I28,Lookups!$B$2:$G$32,3))+IF(J28>0,VLOOKUP(J28,Lookups!$B$2:$G$32,3))-M28M29=IF(G29>0,VLOOKUP(G29,Lookups!$B$2:$G$32,3))+IF(H29>0,VLOOKUP(H29,Lookups!$B$2:$G$32,3))+IF(I29>0,VLOOKUP(I29,Lookups!$B$2:$G$32,3))+IF(J29>0,VLOOKUP(J29,Lookups!$B$2:$G$32,3))N29=IF(G29>0,VLOOKUP(G29,Lookups!B$2:$G$32,3))+IF(H29>0,VLOOKUP(H29,Lookups!$B$2:$G$32,3))+IF(I29>0,VLOOKUP(I29,Lookups!$B$2:$G$32,3))+IF(J29>0,VLOOKUP(J29,Lookups!$B$2:$G$32,3))-M29M30=IF(G30>0,VLOOKUP(G30,Lookups!$B$2:$G$32,3))+IF(H30>0,VLOOKUP(H30,Lookups!$B$2:$G$32,3))+IF(I30>0,VLOOKUP(I30,Lookups!$B$2:$G$32,3))+IF(J30>0,VLOOKUP(J30,Lookups!$B$2:$G$32,3))N30=IF(G30>0,VLOOKUP(G30,Lookups!B$2:$G$32,3))+IF(H30>0,VLOOKUP(H30,Lookups!$B$2:$G$32,3))+IF(I30>0,VLOOKUP(I30,Lookups!$B$2:$G$32,3))+IF(J30>0,VLOOKUP(J30,Lookups!$B$2:$G$32,3))-M30M31=IF(G31>0,VLOOKUP(G31,Lookups!$B$2:$G$32,3))+IF(H31>0,VLOOKUP(H31,Lookups!$B$2:$G$32,3))+IF(I31>0,VLOOKUP(I31,Lookups!$B$2:$G$32,3))+IF(J31>0,VLOOKUP(J31,Lookups!$B$2:$G$32,3))N31=IF(G31>0,VLOOKUP(G31,Lookups!B$2:$G$32,3))+IF(H31>0,VLOOKUP(H31,Lookups!$B$2:$G$32,3))+IF(I31>0,VLOOKUP(I31,Lookups!$B$2:$G$32,3))+IF(J31>0,VLOOKUP(J31,Lookups!$B$2:$G$32,3))-M31M32=IF(G32>0,VLOOKUP(G32,Lookups!$B$2:$G$32,3))+IF(H32>0,VLOOKUP(H32,Lookups!$B$2:$G$32,3))+IF(I32>0,VLOOKUP(I32,Lookups!$B$2:$G$32,3))+IF(J32>0,VLOOKUP(J32,Lookups!$B$2:$G$32,3))N32=IF(G32>0,VLOOKUP(G32,Lookups!B$2:$G$32,3))+IF(H32>0,VLOOKUP(H32,Lookups!$B$2:$G$32,3))+IF(I32>0,VLOOKUP(I32,Lookups!$B$2:$G$32,3))+IF(J32>0,VLOOKUP(J32,Lookups!$B$2:$G$32,3))-M32M33=IF(G33>0,VLOOKUP(G33,Lookups!$B$2:$G$32,3))+IF(H33>0,VLOOKUP(H33,Lookups!$B$2:$G$32,3))+IF(I33>0,VLOOKUP(I33,Lookups!$B$2:$G$32,3))+IF(J33>0,VLOOKUP(J33,Lookups!$B$2:$G$32,3))N33=IF(G33>0,VLOOKUP(G33,Lookups!B$2:$G$32,3))+IF(H33>0,VLOOKUP(H33,Lookups!$B$2:$G$32,3))+IF(I33>0,VLOOKUP(I33,Lookups!$B$2:$G$32,3))+IF(J33>0,VLOOKUP(J33,Lookups!$B$2:$G$32,3))-M33M34=IF(G34>0,VLOOKUP(G34,Lookups!$B$2:$G$32,3))+IF(H34>0,VLOOKUP(H34,Lookups!$B$2:$G$32,3))+IF(I34>0,VLOOKUP(I34,Lookups!$B$2:$G$32,3))+IF(J34>0,VLOOKUP(J34,Lookups!$B$2:$G$32,3))N34=IF(G34>0,VLOOKUP(G34,Lookups!B$2:$G$32,3))+IF(H34>0,VLOOKUP(H34,Lookups!$B$2:$G$32,3))+IF(I34>0,VLOOKUP(I34,Lookups!$B$2:$G$32,3))+IF(J34>0,VLOOKUP(J34,Lookups!$B$2:$G$32,3))-M34M35=IF(G35>0,VLOOKUP(G35,Lookups!$B$2:$G$32,3))+IF(H35>0,VLOOKUP(H35,Lookups!$B$2:$G$32,3))+IF(I35>0,VLOOKUP(I35,Lookups!$B$2:$G$32,3))+IF(J35>0,VLOOKUP(J35,Lookups!$B$2:$G$32,3))N35=IF(G35>0,VLOOKUP(G35,Lookups!B$2:$G$32,3))+IF(H35>0,VLOOKUP(H35,Lookups!$B$2:$G$32,3))+IF(I35>0,VLOOKUP(I35,Lookups!$B$2:$G$32,3))+IF(J35>0,VLOOKUP(J35,Lookups!$B$2:$G$32,3))-M35M36=IF(G36>0,VLOOKUP(G36,Lookups!$B$2:$G$32,3))+IF(H36>0,VLOOKUP(H36,Lookups!$B$2:$G$32,3))+IF(I36>0,VLOOKUP(I36,Lookups!$B$2:$G$32,3))+IF(J36>0,VLOOKUP(J36,Lookups!$B$2:$G$32,3))N36=IF(G36>0,VLOOKUP(G36,Lookups!B$2:$G$32,3))+IF(H36>0,VLOOKUP(H36,Lookups!$B$2:$G$32,3))+IF(I36>0,VLOOKUP(I36,Lookups!$B$2:$G$32,3))+IF(J36>0,VLOOKUP(J36,Lookups!$B$2:$G$32,3))-M36AB9=IF(X9>0,VLOOKUP(X9,Lookups!Q$2:W$16,7,FALSE)*Y9)+IF(Z9>0,VLOOKUP(Z9,Lookups!Q1:W8,7,FALSE)*AA9,0)AB10=IF(X10>0,VLOOKUP(X10,Lookups!Q$2:W$16,7,FALSE)*Y10)+IF(X10>0,VLOOKUP(X10,Lookups!Q2:W9,7,FALSE)*Y10,0)AB11=IF(X11>0,VLOOKUP(X11,Lookups!Q$2:W$16,7,FALSE)*Y11)+IF(X11>0,VLOOKUP(X11,Lookups!Q3:W10,7,FALSE)*Y11,0)AB12=IF(X12>0,VLOOKUP(X12,Lookups!Q$2:W$16,7,FALSE)*Y12)+IF(X12>0,VLOOKUP(X12,Lookups!Q4:W11,7,FALSE)*Y12,0)AB13=IF(X13>0,VLOOKUP(X13,Lookups!Q$2:W$16,7,FALSE)*Y13)+IF(X13>0,VLOOKUP(X13,Lookups!Q5:W12,7,FALSE)*Y13,0)AB14=IF(X14>0,VLOOKUP(X14,Lookups!Q$2:W$16,7,FALSE)*Y14)+IF(X14>0,VLOOKUP(X14,Lookups!Q6:W13,7,FALSE)*Y14,0)AB15=IF(X15>0,VLOOKUP(X15,Lookups!Q$2:W$16,7,FALSE)*Y15)+IF(X15>0,VLOOKUP(X15,Lookups!Q7:W14,7,FALSE)*Y15,0)AB16=IF(X16>0,VLOOKUP(X16,Lookups!Q$2:W$16,7,FALSE)*Y16)+IF(X16>0,VLOOKUP(X16,Lookups!Q8:W15,7,FALSE)*Y16,0)AB17=IF(X17>0,VLOOKUP(X17,Lookups!Q$2:W$16,7,FALSE)*Y17)+IF(X17>0,VLOOKUP(X17,Lookups!Q9:W16,7,FALSE)*Y17,0)AB18=IF(X18>0,VLOOKUP(X18,Lookups!Q$2:W$16,7,FALSE)*Y18)+IF(X18>0,VLOOKUP(X18,Lookups!Q10:W17,7,FALSE)*Y18,0)AB19=IF(X19>0,VLOOKUP(X19,Lookups!Q$2:W$16,7,FALSE)*Y19)+IF(X19>0,VLOOKUP(X19,Lookups!Q11:W18,7,FALSE)*Y19,0)AB20=IF(X20>0,VLOOKUP(X20,Lookups!Q$2:W$16,7,FALSE)*Y20)+IF(X20>0,VLOOKUP(X20,Lookups!Q12:W19,7,FALSE)*Y20,0)AB21=IF(X21>0,VLOOKUP(X21,Lookups!Q$2:W$16,7,FALSE)*Y21)+IF(X21>0,VLOOKUP(X21,Lookups!Q13:W20,7,FALSE)*Y21,0)AB22=IF(X22>0,VLOOKUP(X22,Lookups!Q$2:W$16,7,FALSE)*Y22)+IF(X22>0,VLOOKUP(X22,Lookups!Q14:W21,7,FALSE)*Y22,0)AB23=IF(X23>0,VLOOKUP(X23,Lookups!Q$2:W$16,7,FALSE)*Y23)+IF(X23>0,VLOOKUP(X23,Lookups!Q15:W22,7,FALSE)*Y23,0)AB24=IF(X24>0,VLOOKUP(X24,Lookups!Q$2:W$16,7,FALSE)*Y24)+IF(X24>0,VLOOKUP(X24,Lookups!Q16:W23,7,FALSE)*Y24,0)AB25=IF(X25>0,VLOOKUP(X25,Lookups!Q$2:W$16,7,FALSE)*Y25)+IF(X25>0,VLOOKUP(X25,Lookups!Q17:W24,7,FALSE)*Y25,0)AB26=IF(X26>0,VLOOKUP(X26,Lookups!Q$2:W$16,7,FALSE)*Y26)+IF(X26>0,VLOOKUP(X26,Lookups!Q18:W25,7,FALSE)*Y26,0)AB27=IF(X27>0,VLOOKUP(X27,Lookups!Q$2:W$16,7,FALSE)*Y27)+IF(X27>0,VLOOKUP(X27,Lookups!Q19:W26,7,FALSE)*Y27,0)AB28=IF(X28>0,VLOOKUP(X28,Lookups!Q$2:W$16,7,FALSE)*Y28)+IF(X28>0,VLOOKUP(X28,Lookups!Q20:W27,7,FALSE)*Y28,0)AB29=IF(X29>0,VLOOKUP(X29,Lookups!Q$2:W$16,7,FALSE)*Y29)+IF(X29>0,VLOOKUP(X29,Lookups!Q21:W28,7,FALSE)*Y29,0)AB30=IF(X30>0,VLOOKUP(X30,Lookups!Q$2:W$16,7,FALSE)*Y30)+IF(X30>0,VLOOKUP(X30,Lookups!Q22:W29,7,FALSE)*Y30,0)AB31=IF(X31>0,VLOOKUP(X31,Lookups!Q$2:W$16,7,FALSE)*Y31)+IF(X31>0,VLOOKUP(X31,Lookups!Q23:W30,7,FALSE)*Y31,0)AB32=IF(X32>0,VLOOKUP(X32,Lookups!Q$2:W$16,7,FALSE)*Y32)+IF(X32>0,VLOOKUP(X32,Lookups!Q24:W31,7,FALSE)*Y32,0)AB33=IF(X33>0,VLOOKUP(X33,Lookups!Q$2:W$16,7,FALSE)*Y33)+IF(X33>0,VLOOKUP(X33,Lookups!Q25:W32,7,FALSE)*Y33,0)AB34=IF(X34>0,VLOOKUP(X34,Lookups!Q$2:W$16,7,FALSE)*Y34)+IF(X34>0,VLOOKUP(X34,Lookups!Q26:W33,7,FALSE)*Y34,0)AB35=IF(X35>0,VLOOKUP(X35,Lookups!Q$2:W$16,7,FALSE)*Y35)+IF(X35>0,VLOOKUP(X35,Lookups!Q27:W34,7,FALSE)*Y35,0)AB36=IF(X36>0,VLOOKUP(X36,Lookups!Q$2:W$16,7,FALSE)*Y36)+IF(X36>0,VLOOKUP(X36,Lookups!Q28:W35,7,FALSE)*Y36,0)

 

[GinaWhipp]

Hmm, the only shown there is VLookups and other assorted formulas. Where is the data? If you are expecting Access to translate those formulas during the import that will not happen.

 

[adam087]

I would look at the Access import external data tools. On the Ribbon --> External Data --> Import & Link --> [from] Excel.

Perhaps import it in to a temporary table then have an Append Query already written to move the data across in to your main table(s).

Once you've taken it through the import manually once, step by step, then it gives you the option to save the import steps. It's not 100% automatic, but if you're only importing one Workbook a day it shouldn't be the end of the world, and it gives you some supervision over the import which is probably useful, at least to start with. This will not affect the Excel sheets at all.

Presumably the end-game here is that the team are entering the data directly in to the database?

Hope that helps

/AJ

 

[NFLnut]

I would look at the Access import external data tools. On the Ribbon --> External Data --> Import & Link --> [from] Excel.

Perhaps import it in to a temporary table then have an Append Query already written to move the data across in to your main table(s).

Once you've taken it through the import manually once, step by step, then it gives you the option to save the import steps. It's not 100% automatic, but if you're only importing one Workbook a day it shouldn't be the end of the world, and it gives you some supervision over the import which is probably useful, at least to start with. This will not affect the Excel sheets at all.

Presumably the end-game here is that the team are entering the data directly in to the database?

Hope that helps

/AJ

Thanks, AJ. I tried importing an Excel spreadsheet, but when I do so (perhaps because the spreadsheets are worked upon by my staff with Excel 2002 -- because they prefer the older version and they are reluctant to learn the ribbon -- and the spreadsheets are saved in an .XLS format(?). When I used the "Ribbon --> External Data --> Import & Link --> [from] Excel," I always get an error ("The wizard is unable to access information in the file. Please check that the file exists and is in the correct format"). Not sure why I can't get it to work, but again maybe it is because it is an .XLS (although one of the file types that is listed)?

 

[NFLnut]

Here is the main page of the spreadsheet since the way I posted the HTML above didn't work out (I thought I was using HTMLMaker properly, but apparently wasn't), the best I could figure out how to paste an existing spreadsheet:

														Balance Due	Balance Due
Bill #		Last Name	First Name	MI	Birthday	Proc Code	Proc Code	Proc Code	Proc Code	Diagnosis	Diagnosis	Charge	Spcl Charge	Type # 1	Type # 2
01		Smith	Bob			50501	50501			360.08	360.04	$ 200.00	-	120.00	20.00									-			-	0		0
02		Smith	Mary			50503				360.01		$ 20.00	-														-
03		Jones	Jim			50506	50505			360.03		$ 30.00	-		10.00												-
04		Marks	Cindy			50508				360.05		$ 20.00	-	10.00													-
05		Bellini	Martin			50501	50504			360.07		$ 100.00	-														-
06		Grant	Frances			50503				360.02		$ 20.00	-														-
07												$ -	-														-
08												$ -	-														-
09												$ -	-														-
10												$ -	-														-
11												$ -	-														-
12												$ -	-														-
13												$ -	-														-
14												$ -	-														-
15												$ -	-														-
16												$ -	-														-
17												$ -	-														-
18												$ -	-														-
19												$ -	-														-
20												$ -	-														-
21												$ -	-														-
22												$ -	-														-
23												$ -	-														-
24												$ -	-														-
25												$ -	-														-
26												$ -	-														-
27												$ -	-														-
28												$ -	-													2	-
		[Reserved for Future Use]

<tbody>
</tbody>

 

[NFLnut]

So, I want to daily make Access records only for each customer who has a balance due, and not for the rest of the customers on the sheet. In other words, it is an accounts receivable database that continually adds customers who owe us money. So, in the example above, I would create only 3 Access records and not 6. And I need to differentiate the types of balance dues (Type #1 and #2). Is there a simple automated way for me to do this each day, at the end of the day from each day's spreadsheet?

 

[adam087]

Perhaps saving the XL file as a CSV file first?

/AJ

 

[alansidman]

Need To Export Excel Data To Access Database Daily

Crossposted

See my response in the other forum