Auto refresh and DDE update is not work when running macro
 

Hi all,

I used to monitor my updated stocks quotes in an excel file (stock.xls)
where the data comes from an html websites (iexplorer exports to ms-excel).
Data refresh interval is not less than 1 minute.

I need to save the txt format of my worksheet every one minute for a purpose.
A friend of mine assists me to create a simple macro like this below:

======================
Sub savetext()

'
' kirim Macro
' Macro recorded 3/14/2004 by Eddy
'
' Keyboard Shortcut: Ctrl+k
'
On Error Resume Next

Dim PauseTime, Start, Finish
MsgBox "Starting Macro (save to c:\data.txt)"
PauseTime = 60 ' Set duration.
Start = Timer ' Set start time.
Do While 1

If Timer Start + PauseTime Then
'MsgBox "saving"
Finish = Timer

ActiveWorkbook.SaveCopyAs "c:\data.xls"

Workbooks.Open "c:\data.xls", 3
'ActiveWorkbook("BARU").Activate

'Shell ("del c:\data.txt")
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile "c:\data.txt", True

ActiveWorkbook.SaveAs "c:\data.txt", xlTextMSDOS
ActiveWorkbook.Close True

Start = Timer
Else
DoEvents ' Wait
End If
Loop

End Sub

==================================

This macro really did saving a file called 'data.txt' every one minute
via a workbook backup file called 'data.xls',
but when this macro run, it results the excel's auto refresh is not work

It also occurs when I run a worksheet where the data comes from a DDE server.

I have set macro security level to low and trust every source.


Thanks for any suggestion

EDDY