Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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
0
Andreamary
Asked:
Andreamary
1 Solution
 
Angelo MiletoCommented:
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
 
Rgonzo1971Commented:
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
 
Rgonzo1971Commented:
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
Industry Leaders: 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!

 
Rob HensonIT & Database AssistantCommented:
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
 
AndreamaryAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
AndreamaryAuthor Commented:
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
 
Rgonzo1971Commented:
That's right
0
 
AndreamaryAuthor Commented:
Terrific...thanks, Rgonzo!

Andrea
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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