Solved

Breaks links to external workbook row by row based on date criteria in Excel

Posted on 2014-11-07
9
76 Views
Last Modified: 2014-11-13
I have a spreadsheet that has specific cells linked to a continuously updating external workbook that tracks production output. Column A is a date field. Columns D and E are linked to the external workbook. A sample of the spreadsheet is attached.

I want to be able to get a daily 'snapshot' of production output at the same time each day by automating the following:
When the date changes to the following day, the external links in the row for the previous day break, changing the data in those 2 cells to static data. For example:

As of Nov. 8, 2014 at 12:01 a.m., the following would occur in the sample spreadsheet provided:
In row 12, where Column A = Nov. 7, 2014, cell D12 and cell E12 would convert from an external link to static data.

I don't know if this is a feasible or realistic request, so will be interested to see what the feedback is on this question...

Thanks!
Andrea
Sample.xlsx
0
Comment
Question by:Andreamary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Expert Comment

by:Angelo Mileto
ID: 40429653
It's fairly simple with a quick VBA script to find the row for yesterday and then replace the formula in D & E with the values that are there either with copy/paste values or just insert the value over the formula.

Do you have a simple =[tab]worksheet!A3, or something in D&E? If so, that would work. If you need help writing the vba, let me know.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40430138
Hi,

in a normal module insert this code

 
Sub CallAt00_01()
    Call myProc
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01"
End Sub
Sub StartTempo()
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01"
End Sub
 
Sub StopTempo()
    On Error Resume Next
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01", False
End Sub

Sub myProc()
    Set myrange = Range(Range("A10"), Range("A" & Rows.Count).End(xlUp))
    For Each c In myrange
        If c = Date Then
            c.Offset(0, 3).Resize(1, 2).Value = c.Offset(0, 3).Resize(1, 2).Value
        End If
    Next
End Sub

Open in new window


in the thisWorkbook module insert this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTempo
End Sub

Private Sub Workbook_Open()
Call StartTempo
End Sub

Open in new window


Of course the file must be open by 12:01 to work

Regards
SampleV1.xlsm
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40432069
EDITED Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTempo
End Sub

Private Sub Workbook_Open()
Call StartTempo
End Sub

Open in new window


Sub CallAt00_01()
    Call myProc
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01"
End Sub
Sub StartTempo()
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01"
End Sub
 
Sub StopTempo()
    On Error Resume Next
    Application.OnTime TimeValue("00:01:00"), "CallAt00_01", False
End Sub

Sub myProc()
    Set myrange = ThisWorkbook.Sheets(1).Range(Range("A10"), Range("A" & Rows.Count).End(xlUp))
    For Each c In myrange
        If c = Date Then
            c.Offset(0, 3).Resize(1, 2).Value = c.Offset(0, 3).Resize(1, 2).Value
        End If
    Next
End Sub

Open in new window

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40432527
You may be able to create a Pivot Table on your data. In doing so the values will stay static until the table is refreshed.

Thanks
Rob H
0
 

Author Comment

by:Andreamary
ID: 40440298
Hi Rgonzo,

Thanks for the code. I'm sorry for the delayed response...I've been away.

I'm just trying your code now. Just to confirm, then, that if the spreadsheet is not open at 12:01 but is opened later on that day then the link break will not occur?

Rob, thanks for your suggestion but I'm looking to maintain a history of the daily results, and I don't think a pivot table will do the trick in that regard, would it?

Cheers,
Andrea
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40440364
That' right it will only happen at 00:01

if you want it on opening then pls try to change the open procedure

Private Sub Workbook_Open()
Call myProc
Call StartTempo
End Sub

Regards
0
 

Author Comment

by:Andreamary
ID: 40440469
Perfect. And if it's opened and closed a number of times during the day, that shouldn't be an issue, right, as it only breaks the link based on the date in column A?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40440484
That's right
0
 

Author Closing Comment

by:Andreamary
ID: 40440742
Terrific...thanks, Rgonzo!

Andrea
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

730 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