Hi

I've pulled together some code that will save my spreadsheet into htm
format, and update a =now() field. In addition to that code I have some timer
codes which fire off the =now update, and the htm save.

My problem is, if I am working on another spreadsheet, it places the update
into what I'm working on - rather than the "right" file. I've tried telling
it to go to a named workbook & spreadsheet, but the problem is that the code
flicks to that file, and takes me away from the one I was working on. Very
frustrating.

The update to the now field is meant to happen every 5 minutes.

Is there a way to modify the update code so that I can work uninhibited on
other workbooks?

Cheers, Ty


Sub Every5()

Application.OnTime Now + TimeValue("00:05:00"), "SaveMe"

End Sub


Sub SaveMe()

Call UpdateMe

Dim fRange As Range
Sheets("sheet1").Activate
Application.ScreenUpdating = False
Set fRange = Range(Range("a1"), _
Range("a1").End(xlDown).End(xlToRight))
Workbooks.Open Filename:="d:\data\sc\statuses.xls"
Windows("support centre.xls").Activate
fRange.Copy
Windows("statuses.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="d:\data\sc\statuses.htm",
FileFormat:=xlHtml, _
CreateBackup:=False
ActiveWorkbook.Close savechanges:=False

End Sub


Sub UpdateMe()

'The purpose of this code is to update the date and time
'in an appropriate format.

'This updates G24
Application.Goto reference:="R24C7"
ActiveCell.FormulaR1C1 = "=now()"
Selection.NumberFormat = "dd mmmm yyyy, h:mm AM/PM"

'Go to description box
Application.Goto reference:="R5C3"

End Sub

Tywardreath:

try,

Sub SaveMe()
Call UpdateMe
Dim fRange As Range
Dim wbk As Workbook
Sheets("sheet1").Activate
Application.ScreenUpdating = False
Set fRange = Range(Range("a1"), _
Range("a1").End(xlDown).End(xlToRight))
Set wbk = Workbooks.Open(Filename:="d:\data\sc\statuses.xls" )
fRange.Copy wbk.Worksheets("Sheet1").Cells
Application.DisplayAlerts = False
wbk.SaveAs Filename:="d:d:\data\sc\statuses.htm", FileFormat:=xlHtml, _
CreateBackup:=False
wbk.Close savechanges:=False
Application.DisplayAlerts = True
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Tywardreath" wrote:

Hi

I've pulled together some code that will save my spreadsheet into htm
format, and update a =now() field. In addition to that code I have some timer
codes which fire off the =now update, and the htm save.

My problem is, if I am working on another spreadsheet, it places the update
into what I'm working on - rather than the "right" file. I've tried telling
it to go to a named workbook & spreadsheet, but the problem is that the code
flicks to that file, and takes me away from the one I was working on. Very
frustrating.

The update to the now field is meant to happen every 5 minutes.

Is there a way to modify the update code so that I can work uninhibited on
other workbooks?

Cheers, Ty


Sub Every5()

Application.OnTime Now + TimeValue("00:05:00"), "SaveMe"

End Sub


Sub SaveMe()

Call UpdateMe

Dim fRange As Range
Sheets("sheet1").Activate
Application.ScreenUpdating = False
Set fRange = Range(Range("a1"), _
Range("a1").End(xlDown).End(xlToRight))
Workbooks.Open Filename:="d:\data\sc\statuses.xls"
Windows("support centre.xls").Activate
fRange.Copy
Windows("statuses.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="d:\data\sc\statuses.htm",
FileFormat:=xlHtml, _
CreateBackup:=False
ActiveWorkbook.Close savechanges:=False

End Sub


Sub UpdateMe()

'The purpose of this code is to update the date and time
'in an appropriate format.

'This updates G24
Application.Goto reference:="R24C7"
ActiveCell.FormulaR1C1 = "=now()"
Selection.NumberFormat = "dd mmmm yyyy, h:mm AM/PM"

'Go to description box
Application.Goto reference:="R5C3"

End Sub

Unfortunately the update (to =now()) occurred into the spreadsheet I was
working on (not the file that it should update in).

On the positive side, it did not flick back to the spreadsheet with the
original code.

I think your present code for UpdateMe refers to the activeworkbook
activesheet (which is excel default). You need to make the referenc
clear in this sub. :

--
lotu
-----------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...fo&userid=2375
View this thread: http://www.excelforum.com/showthread.php?threadid=49326