AW: bis auf neuste Datei alle löschen
13.10.2005 12:57:52
lexika
Hallo Micha,
hab mehrere module mit folgendem inhalt
Option Explicit
'Diverse APIs deklarieren
Private Declare
Function CreateProcess Lib "Kernel32" Alias _
"CreateProcessA" ( _
ByVal lpAppName As Long, _
ByVal lpCmdLine As String, _
ByVal lpProcAttr As Long, _
ByVal lpThreadAttr As Long, _
ByVal lpInheritedHandle As Long, _
ByVal lpCreationFlags As Long, _
ByVal lpEnv As Long, _
ByVal lpCurDir As Long, _
lpStartupInfo As STARTUPINFO, _
lpProcessInfo As PROCESS_INFORMATION _
) As Long
Private Declare
Function WaitForSingleObject Lib "Kernel32" ( _
ByVal hHandle As Long, _
ByVal dwMilliseconds As Long _
) As Long
Private Declare
Function CloseHandle Lib "Kernel32" ( _
ByVal hObject As Long _
) As Long
'Einige Konstanten benennen
Private Const NORMAL_PRIORITY_CLASS As Long = &H20&
Private Const INFINITE As Long = -1&
Private Const WAIT_TIMEOUT As Long = 258&
'Einige Datentypen erstellen
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Integer
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type
Public
Sub Diagramm1_1()
Dim lz&, Dber, xv, vx
With Sheets("Sensor 1")
lz = .Cells(.Rows.Count, 8).End(xlUp).Row
Set Dber = .Range("q20:r" & lz)
Set xv = .Range("g21:g" & lz)
Set vx = .Range("f21:f" & lz)
End With
Application.ScreenUpdating = False
With Charts("Diagramm1")
.ChartType = xlLine
.SetSourceData Source:=Dber, PlotBy:=xlColumns
.SeriesCollection(1).XValues = xv
.SeriesCollection(2).XValues = vx
.SeriesCollection(2).AxisGroup = 2
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = "Klimaverlauf Sensor 1"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "..."
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "..."
.Axes(xlCategory, xlSecondary).HasTitle = True
.Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "..."
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "..."
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
.Axes(xlCategory, xlSecondary).CategoryType = xlCategoryScale
.Axes(xlCategory, xlPrimary).TickLabels.Orientation = xlUpward
.Axes(xlCategory, xlSecondary).TickLabels.Orientation = xlHorizontal
.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "dd/mm/yy"
.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "0°C"
.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0%"
.Axes(xlCategory, xlPrimary).MinorTickMark = xlNone
.Axes(xlCategory, xlSecondary).MinorTickMark = xlNone
.Axes(xlCategory, xlPrimary).Border.LineStyle = xlNone
.Axes(xlCategory, xlSecondary).Border.LineStyle = xlNone
End With
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub
Public
Sub Sensor1_alle()
ActiveSheet.Unprotect Password:=""
Worksheets("Sensor 1").Activate
ActiveSheet.Range("F20:I65500").Select
Selection.ClearContents
Range("A10").Select
ChDrive "D:\"
ChDir "D:\excel\Neu\1\"
ShellWait "D:\excel\Neu\1\zusa.bat", 1
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\excel\Neu\1\gesamt.csv" _
, Destination:=Range("F20"))
.Name = "Sensor_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.Refresh BackgroundQuery:=False
End With
Range("F20").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Datum(dd-mmm-yy)"
Range("F21:F65500").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
Selection.AutoFilter
Range("A10").Select
Range("I21:I33345").Select
Application.CutCopyMode = False
Selection.Copy
Range("R21").Select
ActiveSheet.Paste
Range("H21:H33345").Select
Application.CutCopyMode = False
Selection.Copy
Range("Q21").Select
ActiveSheet.Paste
Range("N20").Select
Application.CutCopyMode = False
Selection.Copy
Range("R21:R33345").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlDivide, SkipBlanks:= _
False, Transpose:=False
Range("A10").Select
ActiveSheet.Protect Password:=""
End Sub
Public
Function ShellWait(cmdline As String, Optional ByVal _
bShowApp As Boolean = False) As Boolean
'Diese Funktion führt einen Befehl (in CmdLine) aus.
'Dabei wird das sich öffnende Fenster unsichtbar gemacht.
'Diese Funktion wird erst beendet, wenn der Befehl
'vollständig abgearbeitet ist.
'Speicher reservieren
Dim uProc As PROCESS_INFORMATION
Dim uStart As STARTUPINFO
Dim lRetVal As Long
'Die Datentypen initialisieren
uStart.cb = Len(uStart)
uStart.wShowWindow = Abs(bShowApp)
uStart.dwFlags = 1
'Fenster erzeugen
lRetVal = CreateProcess(0&, cmdline, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, uStart, uProc)
If lRetVal = 0 Then
MsgBox "Starten der Anwendung ist fehlgeschlagen!", _
vbExclamation vbOKOnly
ShellWait = False
Exit Function
End If
'Warten, bis Fenster beendet wurde
'Dabei das eigene Fenster aktualisieren
Do While WaitForSingleObject(uProc.hProcess, 10) = WAIT_TIMEOUT
DoEvents
Loop
'Wenn man solange warten will, bis die Anwendung beendet
'wird und nicht darauf achtet, dass die wartende Anwendung
'dabei absolut zum Stillstand kommt.
lRetVal = WaitForSingleObject(uProc.hProcess, INFINITE)
'Fenster schließen
lRetVal = CloseHandle(uProc.hProcess)
'Rückgabewert setzen
ShellWait = (lRetVal <> 0)
End Function
Danke