Need to create small macro that calculates values of a column using simple excel formulas in Access (Using expressions in Macro I guess)!
I have an excel sheet with "Number" column which has thousands of rows with appx. 700 unique numbers. eg: 9333,9334, 1002, 1689 etc..(random numbers). Along with this row there are other numbered columns (like 3-4 ).
In total there are 10 column calculations based on these 4-5 data columns. I have the excel formulas which gives the values to those 10 columns. But I need this to be automated by using macros in access.
To summarize,
1) DataFile.xls
2) TemplateFile.xls ( Has DataFile.xls fields + other columns fields which need to be calculated (Formulas are already entered). The Auto-fill feature gives the calculations for all the entered rows from data.xls to template file.
-> I need to make this template file to access and the data file will remain in excel. People enter new rows in to data file. with the specific NUMBER field that I mentioned earlier. some may enter 9333 or 1002 etc. with the date. so right now I am manually sorting the data file and checking if there is any new row entered. if yes then copy that row and look its NUMBER and paste it next to the old row of that number to calculate the values for those 10 columns. so , if u noticed here, the calculations should only be done to its corresponding NUMBER value rows. since i have 700 unique numbers and people enter new rows daily for those 700 numbers , i have to manually copy the rows and calculate them and get the desired values from excel formulas I needed.
I want this data file to be set in macro , so that when i run the macro form access, the database reads the data file form the location specified in the macro and automatically adds the new rows to the database and calculate the values for those 10 columns specific to the NUMBER field and store it in the database. Basically the excel formulas I have which are very small functionality like IF(), <= , >= etc. These need to be written in access macro language for the columns which need to be calculated.
This is definitely an cute easy task for those who knew it. I am a java guy and don't know VB.
To get a good idea about the excel formulas i am using, I will paste them below,
=IF(B2=B1,E2-E1,"")
=IF(B2=B1,(E2-E1)/(C2-C1),"")
=IF(AND(G2>=0,G2<19),"OK", "Discontinuous")
=IF(H2="Discontinuous", IF(C2-C1<=1, "DL-Same Day", ""), "")
=IF(H2="Discontinuous", IF(G2<0, IF(((E2/(C2-C1))<19.01), E2, "Exceed to discuss"), ""),"")
=IF(H2="Discontinuous", IF(G2>19, C2-C1, ""), "")
=IF(H2="Discontinuous", IF(G2>19, G2-19, ""), "")
=SUM(M1+F2)
=IF(B2=B1,P2-P1,"")
=SUM(Q2+R1)
Thanks in advance. Any questions I will be happy to reply.
Thanks