Solved

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

Posted on 2014-11-07
9
70 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
9 Comments
 
LVL 1

Expert Comment

by:Angelo Mileto
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Andreamary
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
That's right
0
 

Author Closing Comment

by:Andreamary
Comment Utility
Terrific...thanks, Rgonzo!

Andrea
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
personal workbook 13 28
Scrape Data from a list of hyperlinks 3 28
Checkbox fires off a Statement 7 20
Gantt chart 2 11
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

14 Experts available now in Live!

Get 1:1 Help Now