Refreshing Imported Data

I have used the data imp[ort wizard to import a CSV. The CSV is updated every 20 seconds and rather than set the worksheet to refresh once a min (which seems to be the most frequent i can set it) i would like it to refresh every 20-30 seconds.

This can be done by setting up a looping macro to do this but i would like the worksheet to refresh whilst also being able to enter data on another sheet.

Can this be done?

Yes, refreshing a data query is limited to a minute as a minimum even via code.

However, there is a way of executing code through a timed interval, and the following could be experimented with or adapted as needed -

Set up the following in the Workbook module...

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If bProcessing Then Exit Sub
bProcessing = True
Application.OnTime Now + TimeValue("00:00:30"), "RefreshDataQuery"
End Sub

Change the highlighted green value to the number of seconds required for the refresh.

Set up the following in a new Module...

Public bProcessing As Boolean

Public Sub RefreshDataQuery()
On Error Resume Next
Worksheets("Sheet2").Range("A1").QueryTable.Refresh BackgroundQuery:=True
bProcessing = False
End Sub

Change the green highlighted line to refer to the location of your data query.

I've including a boolean variable (bProcessing) to ensure that these routines are not triggered multiple times, and that there is a steady refresh process.

Not sure what the effect will be when refreshing a large data-set, but the code does refresh in the background so it should be possible to interact with other areas of the Workbook as usual.

The refresh will need just one change anywhere in the Workbook to kick start it, and it will then run continously until the file is closed.

this is certainly interesting i will have a look at this.

i cant seem to get this working.

ive set up as explained bt change the second module to the below

Public Sub RefreshDataQuery()
Dim FileOk As Boolean
FileOk = OpenFile("C:\Plasma\", "ACD_Refresh", ".acsauto")
End Sub

This runs an external file which works in a sub but not when the sheet is changed. Should it run whenever a new is selected either manually or thorugh code?

I'd assumed that the csv file was imported into your Excel Workbook via an Excel data import/query, & not that it was opened externally (presumably OpenFile is another sub somewhere in your code, as it's not an Excel/VBA item).

If you're using my suggested Workbook_SheetChange code, you need to ensure that at the end of the RefreshQueryData routine the bProcessing variable is reset to False.

Assuming that the new CSV data changes something somewhere in the Workbook, the routines should be triggered. If only formulae are updated (and the data is held externally) then perhaps put the Workbook_SheetChange lines under the Workbook_SheetCalculate event instead.