ich importiere aus einer txt nach festen Regeln in unregelmäßigen Intervallen Daten in eine temporäre Tabelle.
Dafür habe ich mir mit dem Makrorekorder folgendes aufgezeichnet:
Sub Datenimport()
'--- Bildschirmaktualisierung aus ----
Application.ScreenUpdating = False
Call Datenlöschen
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\test.txt", Destination:=Range("$A$1")) 'Pfad ist ein Beispiel
.Name = "test_3"
.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 = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
'Tag 2
Range("C88:C172").Select
Selection.Cut Destination:=Range("D1:D85")
'Tag 3
Range("C175:C259").Select
Selection.Cut Destination:=Range("E1:E85")
'Tag 4
Range("C262:C346").Select
Selection.Cut Destination:=Range("F1:F85")
'Tag 5
Range("C349:C433").Select
Selection.Cut Destination:=Range("G1:G85")
'Tag 6
Range("C436:C520").Select
Selection.Cut Destination:=Range("H1:H85")
'Tag 7
Range("C523:C607").Select
Selection.Cut Destination:=Range("I1:I85")
'Tag 8
Range("C610:C694").Select
Selection.Cut Destination:=Range("J1:J85")
'Tag 9
Range("C697:C781").Select
Selection.Cut Destination:=Range("K1:K85")
'Tag 10
Range("C784:C868").Select
Selection.Cut Destination:=Range("L1:L85")
'Tag 11
Range("C871:C955").Select
Selection.Cut Destination:=Range("M1:M85")
'Tag 12
Range("C958:C1042").Select
Selection.Cut Destination:=Range("N1:N85")
'Tag 13
Range("C1045:C1129").Select
Selection.Cut Destination:=Range("O1:O85")
'Tag 14
Range("C1132:C1216").Select
Selection.Cut Destination:=Range("P1:P85")
'Tag 15
Range("C1219:C1303").Select
Selection.Cut Destination:=Range("Q1:Q85")
'Tag 16
Range("C1306:C1390").Select
Selection.Cut Destination:=Range("R1:R85")
'Tag 17
Range("C1393:C1477").Select
Selection.Cut Destination:=Range("S1:S85")
'Tag 18
Range("C1480:C1564").Select
Selection.Cut Destination:=Range("T1:T85")
'Tag 19
Range("C1567:C1651").Select
Selection.Cut Destination:=Range("U1:U85")
'Tag 20
Range("C1654:C1738").Select
Selection.Cut Destination:=Range("V1:V85")
'Tag 21
Range("C1741:C1825").Select
Selection.Cut Destination:=Range("W1:W85")
Range("C1:W85").Select
Selection.NumberFormat = "#,##0.00000"
Selection.Columns.AutoFit
Range("B1:W85").Select
ActiveWorkbook.Worksheets("Import").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Import").Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Import").Sort
.SetRange Range("B1:W85")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Connections("test").Delete
'ActiveWorkbook.Names("test_3").Delete
Range("B1:W85").Select
Selection.Copy
Range("X2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Columns("B:W").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("B:CI").Select
Selection.ColumnWidth = 15
Columns("B:CH").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.Zoom = 80
Range("B3:CH23").Select
End Sub
Nun möchte ich die Auswahl "Range("B3:CH23").Select" dynamisch nach der Anzahl der importierten Tage ändern.
Beispiel: Werden Daten für 2 Tage importiert, soll der Bereich B3:CH4 markiert werden. Bei z. B. 10 Tagen soll der Bereich "Range("B12:CH12").Select" markiert werden.
Kann mir dabei bitte jemand helfen?
Vielen dank im Voraus.
Viele Grüße
Ralf