AW: QueryTable
11.01.2019 09:29:58
Fennek
Hallo,
für QueryTable lässt sich eine "Refresh"-Zeit einstellen.
Aus dem Archiv:
https://msdn.microsoft.com/de-de/vba/excel-vba/articles/querytable-object-excel
Refresh Periode
1x .Add, dann Refresh
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;X:TemperaturMusikraum.csv", Destination:=Range("$A$2"))
.Name = "TemperaturMusikraum"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 9, 9, 9, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
########## Beispiel für ein periodische update
class-modul: http://www. _
office-loesung.de/p/viewtopic.php?f=166&t=759678
Private Sub Workbook_Open()
c00 = ThisWorkbook.Path & "\data.csv"
If Dir(c00) = "" Then
CreateObject("scripting.filesystemobject").createtextfile(c00).write "aa,bb,cc,dd" & _
vbLf & "aa,bb,cc,dd"
With ThisWorkbook.Sheets(1).QueryTables.Add("TEXT;" & c00, ThisWorkbook.Sheets(1).Cells( _
1))
.RefreshPeriod = 20
.Refresh False
End With
End If
Set Q_T.qt = ThisWorkbook.Sheets(1).QueryTables(1)
End Sub
################ events ###############
Public WithEvents qt As QueryTable
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
End Sub
Private Sub Workbook_Open()
c00 = ThisWorkbook.Path & "\data.csv"
If Dir(c00) = "" Then CreateObject("scripting.filesystemobject").createtextfile(c00).write " _
aa,bb,cc,dd" & vbLf
With ThisWorkbook.Sheets(1).QueryTables
If .Count = 0 Then .Add "TEXT;" & c00, .Parent.Cells(1)
Set qt = .Item(1)
End With
With qt
.RefreshPeriod = 20
.Refresh False
End With
End Sub
mfg