Solved

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

Posted on 2016-07-26
22
43 Views
Last Modified: 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.
0
Comment
Question by:Lawrence Salvucci
  • 11
  • 11
22 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729393
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729409
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729458
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729461
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729467
I believe the source file has to be saved so that the Edit Links Window can see the link and its path.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729469
I did that. I closed the file and reopened it and still no links are showing up.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729473
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729475
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729492
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729506
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729511
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729514
No the comments are not fed back into Access. Once the excel file is created then everything stays in Excel.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 41729530
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729539
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729552
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729559
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729562
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729567
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41729576
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729597
Let me see what I can come up with along with your code. I will post back shortly.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41729659
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41729705
Excellent, glad to be of assistance.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now