• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Open Excel file from Access and change the formula to point to a different file

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.
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 11
  • 11
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
With the Excel file containing the link to the daily file, you can use the Edit Links wizard to change all links.

On the Data ribbon select Edit Links. Choose the file and then click Change Source. Browse to the new file and click OK.

Thanks
Rob H
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I would prefer this to be part of the export process instead of users having to change this manually. And there are no links to these files. It's just part of the VLOOKUP formula so the files won't show up in the Edit Links section.
0
 
Rob HensonIT & Database AssistantCommented:
If your lookup formula includes reference to another file, a link will be created and it will show in the Edit Links window.

Does the export from Access create the file or does it populate data into an already existing file/template?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I just created a formula with a link to another file and I don't see it in the Edit Links window. In fact the Edit Links button is shaded out because there are no links in this file. Am I missing something then?
0
 
Rob HensonIT & Database AssistantCommented:
I believe the source file has to be saved so that the Edit Links Window can see the link and its path.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I did that. I closed the file and reopened it and still no links are showing up.
0
 
Rob HensonIT & Database AssistantCommented:
I was wrong on that anyway. Apologies!

I have just tried opening two new files book1 and book2 and in book1 I created a simple

='book2'Sheet1!$A$1

by pressing = and then selecting a cell in book2. When I looked at the Edit Links in book1, I had a link to book2.

The link will only show in the destination workbook, book1 in this example, and not in the source
workbook.

Does your Access routine create a new file or just another sheet within an existing file?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No worries. I was worried I was doing something wrong.


It creates a whole new file but just changing the date part of the file name to the current date.
0
 
Rob HensonIT & Database AssistantCommented:
As it is a new file, the formulas have to be created from scratch rather than just amended but you want that to be part of the export routine.
0
 
Rob HensonIT & Database AssistantCommented:
I assume your previous day comments don't get fed back into Access.

I know enough about Excel VBA to be able to generate the formula but I don't know how that would be triggered by Access.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes I want it to be part of the routine. Maybe after the file is created it then opens Excel and puts the formulas in column R for all the rows with data in them? But then it needs to be able to reference the previous days file in the formula. I was tinkering with the formula in Excel directly and this is what I came up with for a formula but the other file needs to be opened for it to work since the INDIRECT function needs the source file opened. If I use this formula then it will automatically reference the previous days file and I won't have to worry about changing the date in the formula's file path. The only catch is how to get this formula in the newly created file and then open the previous days file so it will update the formulas results. And the other catch would be weekends. In the formula I show below it's just deducting 1 day from the current date but since this export function won't run on weekends then it doesn't know to back up to Friday's date as the previous date for Monday's. So that would be another catch about using the formula below.


VLOOKUP(E2,INDIRECT("'[Backorder Report - "&TEXT(TODAY()-1,"mm-dd-yy")&".xls]qryBackorderReportCExcel'!$E$2:$R$1000"),14,FALSE)

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No the comments are not fed back into Access. Once the excel file is created then everything stays in Excel.
0
 
Rob HensonIT & Database AssistantCommented:
How about as part of the routine, import the previous day file back into Access as a temporary table?

Then you can include a field in the query pulling the comments from the previous day.

To get over the weekend issue:

VLOOKUP(E2,INDIRECT("'[Backorder Report - "&TEXT(TODAY()-IF(WEEKDAY(TODAY(),1)=2,3,1),"mm-dd-yy")&".xls]qryBackorderReportCExcel'!$E$2:$R$1000"),14,FALSE)

The added section checks the Weekday of TODAY and if it is 2 (Monday) it deducts 3 else it deducts 1.

EDIT: Realised the deducted value was wrong way round in formula.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I like that idea actually. I will have to figure out a way to pull the correct file back into Access using yesterday's date, and excluding weekends as well. But that actually is a good idea. Then I can push out the new file with the comments already loaded into it. But then if I do that do I really need that formula in excel then? When I think the process through it doesn't seem like I will need it, correct? But if that's the case I will keep that formula for reference just in case I could use it elsewhere. Thank you for the correction to get over the weekend hump.
0
 
Rob HensonIT & Database AssistantCommented:
That was indeed the suggestion, if previous day comments are already in the file there would be no need for the formula; by the sounds of things they get overwritten anyway once the file is distributed.

Advantage of it being linked / downside of it not being linked: if the user decides on Thursday to change the comments on lets say a Tuesday day file the change would not get reflected in the Wednesday file for uploading to be included in the Thursday file. Does that make sense???
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That makes total sense. But there will be no need to change the comments on the previous days files. If it's not on the current days file then that means the order has shipped so no need to comment on it anymore. So there shouldn't ever be a case where they would need to go back and enter comments on an old file. I think I can handle it from here. Just need to work on the Access code to pull the correct file back into a temp table. And then link that to the query that I use to export to the newly created file. Thanks for helping with the formula and brainstorming a better solution. I really appreciate the help!
0
 
Rob HensonIT & Database AssistantCommented:
No worries.

It might be worth waiting to see if any Access experts can contribute for importing the previous day file.

I assume that your export routine creates the export file-name using current date so no doubt you will be able to create the equivalent import fie-name using the same procedure but with an adjusted date.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
True. I will leave this ticket open because I could use some help getting the file import code created. This is what I am currently using to export the query into excel:


Function ExportBackorderReport()
Dim strDate As String
strDate = Format(Date, "mm-dd-yy")
    DoCmd.OutputTo acOutputQuery, "qryBackorderReportCExcel", acFormatXLS, "\\BCAR1\BC-Net\Supply Chain\Production Control\DPM Files\DPM Notes\Backorder Report - " & strDate & ".xls", False
End Function

Open in new window

0
 
Rob HensonIT & Database AssistantCommented:
I don't know if there is a WEEKDAY equivalent in Access VBA

In Excel VBA you can do the following:

Weekday(Date, vbSunday)

Open in new window


The second argument dictates which day of the week is day 1. In this case testing for Weekday being 2 would check for Date being a Monday.

Stabbing a  guess at adapting your code:

Function ExportBackorderReport()
Dim strPriorDate As String
Deduct = 1
If Weekday(Date,vbSunday) = 2 Then Deduct = 3
strPriorDate = Format(Date - Deduct, "mm-dd-yy")
Import File....

End Function

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Let me see what I can come up with along with your code. I will post back shortly.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I got it working perfectly for the import. Your code was the same in Access VBA. So here is the solution I came up with. It will import the previous days file and skip the weekends using your code. I just tested it and it works fine. Now I just link it up with the output query and I'm good to go. Thanks again for all your help!!!

Sub ImportBackorderReport()
Dim strDate As String
Dim strPriorDate As String
Dim Deduct As String
Deduct = 1
If Weekday(Date, vbSunday) = 2 Then Deduct = 3
strPriorDate = Format(Date - Deduct, "mm-dd-yy")

strDate = Format(Date, "mm-dd-yy")

Dim db As DAO.Database
Set db = CurrentDb

On Error Resume Next:   db.TableDefs.Delete "tblTempBackOrder":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblTempBackOrder", _
    filename:="\\BCAR1\BC-Net\Supply Chain\Production Control\DPM Files\DPM Notes\Backorder Report - " & strPriorDate & ".xls", _
    HasFieldNames:=True, _
    Range:="qryBackorderReportCExcel!A:R"
db.TableDefs.Refresh
db.Close:   Set db = Nothing
End Sub

Open in new window

0
 
Rob HensonIT & Database AssistantCommented:
Excellent, glad to be of assistance.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now