asked on

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.

Rob Henson

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

Lawrence Salvucci

ASKER

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.

Rob Henson

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?

Lawrence Salvucci

ASKER

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?

Rob Henson

I believe the source file has to be saved so that the Edit Links Window can see the link and its path.

Lawrence Salvucci

ASKER

I did that. I closed the file and reopened it and still no links are showing up.

Rob Henson

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?

Lawrence Salvucci

ASKER

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.

Rob Henson

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.

Rob Henson

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.

Lawrence Salvucci

ASKER

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

Lawrence Salvucci

ASKER

No the comments are not fed back into Access. Once the excel file is created then everything stays in Excel.

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

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.

Rob Henson

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???

Lawrence Salvucci

ASKER

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!

Rob Henson

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.

Lawrence Salvucci

ASKER

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

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

Let me see what I can come up with along with your code. I will post back shortly.

Lawrence Salvucci

ASKER

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

Rob Henson

Excellent, glad to be of assistance.