Open Excel file from Access and change the formula to point to a different file
Posted on 2016-07-26
I have a query that is exported into Excel from Access every morning. The file is exported into an Excel file with the same file name except it has the current date in the file name. For example today's file name is "Backorder Report - 07-26-16.xls". And then tomorrow's file will be "Backorder Report - 07-27-16.xls". Column R is the first blank column in the file and that is where my users will be entering comments for that particular row in the excel file. So when the file gets generated the next morning I want to be able to put a formula in column R that will do a "VLOOKUP" from the previous days file and pull in any comments for that record into the new files column R. If that record doesn't exist in the previous days file then that cell in column R will just be blank. They can overwrite the formula in that new file with any comments if there aren't any comments being pulled in from the previous day. Then when the new file is created the next morning it will pick up those comments. So what I need to do is when the file is exported to excel that morning I need to open that file up and put the formula in column R for all the records in that file and point the VLOOKUP to the previous days file. Since the date is part of the file name it will need to change every day this formula is put into column R. And since I am running this from Access I would like this to also run from Access.
Not sure if this is possible but can you put a formula in the query that I am exporting but have it change the date portion of the file name so it will pull from the file from yesterday? Seems like that would be the easiest solution but not sure if that's possible. And if it is I am not sure how to change the file name to the previous days name since the date is different for each file.