asked on

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

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

Angelo Mileto

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.

Rgonzo1971

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

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

Start Free Trial

Rob Henson

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

Andreamary

ASKER

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

Rgonzo1971

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

Andreamary

ASKER

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?

Rgonzo1971

That's right

Andreamary

ASKER

Terrific...thanks, Rgonzo!

Andrea