AW: Eingabefeld wenn
23.04.2010 07:51:51
David
Hallo Sascha,
so?
Option Explicit
Sub import200TXTself()
Dim strFile As String
Dim lngRow As Long
Dim i As Long
On Error GoTo ErrExit
strFile = Application.GetOpenFilename("Text Dateien (*.txt),*.txt")
'strFile = "C:\INkontrol\200.txt"
If strFile = CStr(False) Then GoTo ErrExit
With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
With Sheets("Import 200")
lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
With .QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=.Cells(lngRow, 1))
.Name = Left(strFile, Len(strFile) - 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 = 2
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 1, 9, 1, 9, 1, 2, 5, 9, 1, 9, 1, 1, 1, 9)
.TextFileFixedColumnWidths = Array(3, 21, 7, 1, 7, 9, 8, 3, 8, 6, 9, 18, 3, 8, 20)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
.Columns("A:J").AutoFit
End With
ErrExit:
With Err
If .Number 0 Then MsgBox "Fehler " & .Number & vbLf & vbLf & _
.Description & vbLf & vbLf & "In Prozedur (import200TXTself) in Modul2", _
vbExclamation, "Fehler in Modul / Import200TXTself"
End With
For i = 4 To Range("M65535").End(xlUp).Row
If Cells(i, 13) "" Then Cells(i, 14) = Date
Next
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
Ich habe zusätzlich noch die Bildschirmaktualisierung (Screenupdating) abgeschaltet, das beschleunigt das Makro und verhindert etwaiges "Flackern" des Monitors.
Gruß
David