Solved

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

Posted on 2014-11-07
9
73 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
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 49

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 49

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 32

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 49

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 49

Expert Comment

by:Rgonzo1971
ID: 40440484
That's right
0
 

Author Closing Comment

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

Andrea
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 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