Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22

    Thumbs up Need to create small macro that calculates values of a column using simple excel formulas!

    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

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    first - this has nothing to do with macros by the Access definition of that object type...wrong term
    second - just the issue of attempting to use excel as the front end / data entry area of an Access database is its own issue - and you need to research that topic alone in terms of linking an Access table to an excel sheet
    third - all of your formulas are calculated fields in a query; which is fairly straight forward....

    not sure how / when that query is to be triggered to run and display as that is conditional on your user experience.......

    the Access implementation of this would be to present the user an Access form for data input - and then trigger the query to calculate the fields needed - and then display either in a refreshed form or a new form that is opened....

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sounds kind of interesting. The programming shouldn't be hard, but it will be tricky because Excel has a previous row, where Access does not have a previous records per se, the previous record can change depending on the record set order.

    Can you post the DataFile.xls, TemplateFile.xls and Access files with test records? Don't need thousands of rows - maybe 50.

  4. #4
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22

    Question Thanks for the reply. Ya tricky part is to find the previous row to compare.

    Quote Originally Posted by ssanfu View Post
    Sounds kind of interesting. The programming shouldn't be hard, but it will be tricky because Excel has a previous row, where Access does not have a previous records per se, the previous record can change depending on the record set order.

    Can you post the DataFile.xls, TemplateFile.xls and Access files with test records? Don't need thousands of rows - maybe 50.

    Thanks again for your reply. I am gonna provide a link for those sample files. I don't have any problem if we add any additional columns like unique id or manipulate something. The end result is important
    Sorry for the late reply though.

    yes you are right "#ssanfu" It is a small interesting macro in itself. I am attaching the Data and template file with the excel formulas. Along with that i have also attached the updated version of data file in .xlsm format. its same as the date.xls file but i have just added another column(first) called "ID" just so we have a unique id and I have sorted it in an Date ascending order.

    Download Link: (Dropbox)- (Couldn't attach the .Zip or excel files here)
    https://www.dropbox.com/s/qpuvz5x9nf...Files.zip?dl=0

    Kindly let me know if you have any questions regarding the requirement.

    Thanks
    Last edited by hacerz; 02-07-2015 at 11:10 AM. Reason: Attachments

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It took me a while to decipher the spreadsheets and get a little understanding about what you are trying to accomplish.
    But I didn't see the Access dB.

    Quote Originally Posted by NTC View Post
    <snip>
    second - just the issue of attempting to use excel as the front end / data entry area of an Access database is its own issue - and you need to research that topic alone in terms of linking an Access table to an excel sheet
    <snip>
    I re-read your post and have to agree with NTC - keeping the data in Excel and doing the calculations in Access is a problem.
    I think it would be easier to write code in Excel to manage the new entries.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Another voice advocating one app instead of two collaborating. I inherited a partially developed db that used Excel workbooks as part of the user interface for data entry and reporting which required tons of code to feed the data in and out of Access tables. The rationale behind this setup was because of all the intricate formulas required and cell referencing in Excel was so easy. After about 3 months I decided this was nuts and eliminated the Excel component.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22

    Exclamation Linking the excel to Access DB is already built-in. Just have to use Import external data option !

    Quote Originally Posted by ssanfu View Post
    It took me a while to decipher the spreadsheets and get a little understanding about what you are trying to accomplish.
    But I didn't see the Access dB.


    I re-read your post and have to agree with NTC - keeping the data in Excel and doing the calculations in Access is a problem.
    I think it would be easier to write code in Excel to manage the new entries.

    The formulas for the calculations are already in excel but the calculation for each numbers has to be done manually one by one and there are like almost 700 unique numbers. Its nothing like I have to go only with the linking of the excel file to the access db. i just wrote that as one of the options. The data excel file will be available to me everyday with new rows for any number of given numbers. we can have all the current data imported to access and then start comparing the excel with the current db for new rows and do the calculation or any other option which suits best. But if I got your question right , on how to link the excel file, its just simple.

    Note: The data can be access and does not have to be in excel. But we get the data file with new rows each day. so we can transfer the whole data file each day and then do the calculations or just have the excel file linked in access rather than importing each day. either of the option is fine with me.

    Please let me know if you didn't get anything.

    Thanks

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How does this data get into the Excel file? If this is provided to you by an outside source and you have no control over that, then if the Excel data structure is simple enough, a link should be adequate to allow manipulation of the data in Access. Just can't edit the Excel file through the link.

    However, Excel sheet cannot hold as many records as Access table. How big do you expect the Excel file to become?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    How does this data get into the Excel file? If this is provided to you by an outside source and you have no control over that, then if the Excel data structure is simple enough, a link should be adequate to allow manipulation of the data in Access. Just can't edit the Excel file through the link.

    However, Excel sheet cannot hold as many records as Access table. How big do you expect the Excel file to become?
    The data file is modified by couple of people daily and over time it will grow but of course it won't pass the Excel limit if there is any. Honestly, if there is any better approach, I am willing to go for it. There is no limitation from my side except the data file is in Excel format and people enter the new rows and I get access to it to calculate those 10 columns using those formulas but it's just that I have to manually compare each number row to its previous row eg: for 1002, calculate the values based on the new row and the previous row (can be yesterday's or older than that). Since there are many numbers, I have to do them manually for each corresponding previous row. Access would make it automation. Please let me know if any questions or any suggestions to make it better. The only thing can't change is the data file is an Excel file.

    Thanks in advance.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Well, Excel allows you to copy formulas by dragging cell so carrying the formula down to new records is fairly quick and simple. The equivalent calc in Access would be a nested subquery. Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

    If you can't eliminate the Excel component, a link and query should be able to accomplish. But what benefit is gained by linking Access to the Excel file? Are there other tables in Access related to the Excel data? Easier report construction?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22
    Quote Originally Posted by June7 View Post
    Well, Excel allows you to copy formulas by dragging cell so carrying the formula down to new records is fairly quick and simple. The equivalent calc in Access would be a nested subquery. Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

    If you can't eliminate the Excel component, a link and query should be able to accomplish. But what benefit is gained by linking Access to the Excel file? Are there other tables in Access related to the Excel data? Easier report construction?
    The benefit I want is, not to manually search each new row and its corresponding previous row based on the number and have the result calculated, as all the numbers with many records are in the same excel file. So Macro or query whatever we may call them can automatically search a new record comparing to the present database with the new data file and search each row's corresponding old row for that particular number and calculate the value and do the same for other new rows with different numbers.

    so on a click of a button which runs the macro/query should resolve this task and get me the result for those result Columns. to make it more simple, how can we get the previous row meaning, how can we cell reference the previous row based on some column. eg: i can get the value of the previous row in the excel by typing A2-A1. how canI do this in access. after this i want to do the same as we can do using lookup's in excel to find the previous row with the same number.
    Hope you got my point.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Note: The data can be access and does not have to be in excel. But we get the data file with new rows each day.
    Does this mean that the date (Cax Time) will be increasing? If the last row date was 12/31/2014, the next day new rows are added that will be 1/1/2015 and not 11/12/2014? Or can the dates for the new rows be any date?

    In Data.xlsx, there are 65 rows for "Number" = 1002. The (first) column "Segment Data" has 100 entered, then there are formulas.
    When "Number" = 1006 (54 records), what happens to the (first) column "Segment Data"? Is 100 entered again or do the formulas continue?

    Can you provide a "Template.xlsx" example with "Number" = 1002 and 1006 rows so I can see how the formulas work when "Number" column changes?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What do you mean by 'previous' row - the one immediately above and adjacent to current row or could be any row above the current row?

    In Access there is no 'cell reference' - obtaining data from another record of the same table requires nested subquery - the referenced link has example - or domain aggregate functions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22

    Exclamation Formulas are the same and the first value 100 is just an hard coded I put there !

    Quote Originally Posted by ssanfu View Post
    Does this mean that the date (Cax Time) will be increasing? If the last row date was 12/31/2014, the next day new rows are added that will be 1/1/2015 and not 11/12/2014? Or can the dates for the new rows be any date?

    In Data.xlsx, there are 65 rows for "Number" = 1002. The (first) column "Segment Data" has 100 entered, then there are formulas.
    When "Number" = 1006 (54 records), what happens to the (first) column "Segment Data"? Is 100 entered again or do the formulas continue?

    Can you provide a "Template.xlsx" example with "Number" = 1002 and 1006 rows so I can see how the formulas work when "Number" column changes?

    you can ignore the "100" value infact you can assume the first value as 100 if you want. I just had that value in the template file so i started like that. we can ignore and have 100 for a start value. So the template file would look like the same for which I provided the link. Please let me know any quesitons

  15. #15
    hacerz is offline Novice
    Windows XP Access 2013
    Join Date
    Jan 2015
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    Does this mean that the date (Cax Time) will be increasing? If the last row date was 12/31/2014, the next day new rows are added that will be 1/1/2015 and not 11/12/2014? Or can the dates for the new rows be any date?

    In Data.xlsx, there are 65 rows for "Number" = 1002. The (first) column "Segment Data" has 100 entered, then there are formulas.
    When "Number" = 1006 (54 records), what happens to the (first) column "Segment Data"? Is 100 entered again or do the formulas continue?

    Can you provide a "Template.xlsx" example with "Number" = 1002 and 1006 rows so I can see how the formulas work when "Number" column changes?

    forgot to answer your first part of the question. yes the dates would be increasing and when the new rows are entered they will enter that new date so it wont be any date and also it is possible that there can be multiple rows with the same date. eg: some one can enter 3 rows for number 1002 same day with the same date. but our new record will be the latest(last entered) one and its previous row will be the one which was entered second today. so the first entered row for today will be used to calculate it with the yesterday's row if available or may be day before yesterday. then the row which is entered second will be used to calculate with the first one. similarly the last entered will be used to calculate with the second entered row. Hooe you get this.

    Thanks

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-16-2014, 10:12 AM
  2. using macro to rename column values in Access
    By coolbear91 in forum Programming
    Replies: 1
    Last Post: 02-20-2013, 11:10 PM
  3. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  4. Can't create a simple macro......
    By Sarge, USMC in forum Access
    Replies: 1
    Last Post: 01-05-2011, 11:48 AM
  5. Replies: 3
    Last Post: 12-21-2010, 11:52 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums