PaddyStan wrote:
I need something to record what the value was in a cell in Excel at 00:01
on a Monday and record the value in the same cell at 11:59 the following Sunday and calculate the difference between the two.
Is this possible?
Write a batch file. Copy this into notepad:
del "Monday 00-01.xlsx"
copy "my workbook.xlsx" "Monday 00-01.xlsx"
Save that to a file that ends with .bat (and not .txt) in the same directory
as your spreadsheet. (Replace "my workbook.xlsx" with the actual name of
your spreadsheet.) Schedule that to run every Monday at 00:01. Then copy
this into notepad:
del "Sunday 11-59.xlsx"
copy "my workbook.xlsx" "Sunday 11-59.xlsx"
Save that to a different file that also ends with .bat. Schedule that to run every Sunday at 11:59. Then in your report spreadsheet, add this sub:
Sub compare()
Dim monday As Workbook, sunday As Workbook, target As Worksheet
Set target = ActiveSheet
Set monday = Workbooks.Open("D:\Monday 00-01.xlsx", ReadOnly:=True, _
AddToMru:=False)
Set sunday = Workbooks.Open("D:\Sunday 11-59.xlsx", ReadOnly:=True, _
AddToMru:=False)
target.Range("A1").Value = sunday.ActiveSheet.Range("A1").Value - _
monday.ActiveSheet.Range("A1").Value
monday.Close
sunday.Close
Set monday = Nothing
Set sunday = Nothing
End Sub
Run that whenever you want to see the results. (There are better ways to do this, but this way doesn't require you to save the data anywhere else.)
--
I don't know and I have no opinion.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)