I have an application were I need to save the data in a
spreadsheet two times a day at specifc times. I need to
save them off as differnt name+date+time. My problem is
that when I do a comparison on the time, it executes the
function (msg boxs indicator) but the it doesnt create
the file. If I execute this with a button on the screen,
it will work everytime.
My Goal : I have some live data (DDE values) that I need
to save off in a static file of sometype, maybe there is
another solution that I havent considered, any
suggestions are thankfully welcomed.
-----------------------------------------
Execution command in a cell.
=IF(D80=E80,autosave(),"NOT RUNNING")
-----------------------------------------
Converting time value to a number for
the comparsion
=TIMEVALUE(D81)
=TIMEVALUE(E81)
-----------------------------------------
Function autosave()

Dim strdate As String
strdate = Format(Date, "ddd, mm dd yyyy")
ActiveWorkbook.SaveCopyAs Filename:="C:\temp\Myreport " &
strdate & ".xls"
MsgBox "Report Has Run"
End Function
------------------------------------------

Hi

Have you tried the OnTime event of the application class? In you
situation, if I have understood your requirement, what you can do i
write an OnTime event procedure - the code that you have is pretty muc
ok (I don't believe that there might be a need for comparing times sinc
you can specify the time &/or schedule in the OnTime event procedures).

With that in place, the code should run at your nominated time and o
your nominated schedule! I haven't used the schedule bit but if you d
not want to try that, you may want to write 2 separate OnTime event
for the 2 times that you may be interested in!

This is on the presumption that your spreadsheet is always open at th
scheduled times.

This could get a bit tricky in that the copy of the spreadsheet tha
has got saved will also have this event in it - you may want t
consider manually removing the event from the saved copy.

See "http://www.cpearson.com/excel/vbe.htm" on how to remove VB
procedures/modules for workbooks. You may want to consider includin
some of those code to remove this event from the saved copy.


Hope this helps!

Best regards


Deepak Agarwa

--
agarwaldv
-----------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...fo&userid=1134
View this thread: http://www.excelforum.com/showthread.php?threadid=26665