Loading a text file regularily.

  • Thread starter Thread starter Jean-Christophe
  • Start date Start date
J

Jean-Christophe

I have a text file filled with ASCII numeric data updated regularily.
Now I want a chart to display these values as they change,
so I need to automatically reload the data file every 10 minutes.
How can I do this under Excel ?
TIA
 
Jean-Christophe,

Write a macro to import the text file and place the data as needed into a chart. Then use the
Application.Ontime method at the end of the macro to schedule itself for 10 minutes in the future.

HTH,
Bernie
MS Excel MVP
 
Write a macro to import the text file and place the data as needed into achart.  Then use the
Application.Ontime method at the end of the macro to schedule itself for 10 minutes in the future.
Click to expand...

Thanks Bernie.
Unfortunately I'm not advanced enough to do that from scratch.
May I ask if you have a sample code for this macro ?
 
Jean-Christophe,

Here is some sample code: set up your template so that the first sheet will contain the data (but is
currently empty) and extract the data that you want to graph onto another sheet using links.
Pasting the values will update those links and the graphs. The specific code to open your data file
depends on the structure - use the macro recorder. This shows how to schedule the next running of
the macro, and how to cancel it, too.

Option Explicit
Dim NextTime As Date

Sub GetData()
Dim myFileName As String

myFileName = "C:\folder\whatever.txt"
Workbooks.OpenText Filename:= _
myFileName, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
ActiveSheet.Range("A1").CurrentRegion.Copy
ThisWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
Application.DisplayAlerts = False
ActiveWorkbook.Close False
ThisWorkbook.SaveCopyAs "C:\StorageFolder\Filename" & Format(Now(), "yyyy-mm-dd-hh-mm") &
".xls"
ThisWorkbook.Sheets(1).Cells.ClearContents

NextTime = Now() + TimeValue("00:10:00")
Application.OnTime NextTime, "GetData"
End Sub

Sub CancelRead()
On Error Resume Next
Application.OnTime NextTime, "GetData", schedule:=False
End Sub


HTH,
Bernie
MS Excel MVP


Write a macro to import the text file and place the data as needed into a chart. Then use the
Application.Ontime method at the end of the macro to schedule itself for 10 minutes in the future.
Click to expand...

Thanks Bernie.
Unfortunately I'm not advanced enough to do that from scratch.
May I ask if you have a sample code for this macro ?
 
On 18 juin, 16:13, "Bernie Deitrick" <deitbe @ consumer dot org>

I made it work but I could not auto-update below 1 min
i.e, say 15 seconds - Now I'll try your code.
Thank you for your help Bernie.
 
Here is some sample code: set up your template so that the first sheet will contain the data (but is
currently empty) and extract the data that you want to graph onto anothersheet using links.
Pasting the values will update those links and the graphs.  The specific code to open your data file
depends on the structure - use the macro recorder.  This shows how to schedule the next running of
the macro, and how to cancel it, too.
Click to expand...

Thanks, just what I needed - it works pretty well now !
I was wondering if there is a way to switch from Basic to C ?
 
Back
Top