Hallo, ich versuche mich gerade an folgender Aufgabenstellung:
Alle .csv Datein eines ausgewaehlten Ordners oeffnen und dann das dann nach XXXProgramXXstartsXXXX
startende Programm abspulen fuer jede Datei. Danach die Datein nicht schliessen oder speichern.
Leider bekomme ich keinen runtime error (1004).
Hat jemand eine Idee? Meine VBA-Kenntnisse sind leider noch sehr uebersichtlich. Vielen Dank!
Sub LoopAllExcelFilesInFolder()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
myExtension = "*.csv"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
'XXXXXXXXXXXXXXXXXXXXProgramXXstartsXXXXXXXXXXXXXXXXXXXXXXXXX
Dim i As Integer
Dim j
i = 0
j = 0
Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
For i = 19 To 522
' If Then
' Rows(i).Delete
' End If
If Cells(i, 4).Value >= Cells(i, 5).Value And Cells(i, 4).Value <= Cells(i, 6).Value Or Cells(i, 4).Value = "0" Or Cells(i, 7).Value = "-" Or Cells(i, 7).Value = "mm" Or Cells(i, 4).Value = "Value" Or Cells(i, 7).Value = "mbar" Or Cells(i, 7).Value = "Grad" Or Cells(i, 7).Value = "ccm/min" Or Cells(i, 6).Value = "" Or Cells(i, 5).Value = "" Or Cells(i, 2).Value = "OP1200OS" Or Cells(i, 2).Value = "OP1200CS" Then
'Range(Cells(i, 1), Cells(i, 8)).Interior.Color = RGB(0, 255, 0)
Rows(i).Delete
' Or Cells(i, 4).Value = "1.3" Or Cells(i, 6).Value = "" Or Cells(i, 5).Value = "" Or Cells(i, 7).Value = "kN" Or Cells(i, 7).Value = "Nm"
Else
Range(Cells(i, 1), Cells(i, 8)).Interior.Color = RGB(255, 0, 0)
i = i + 1
End If
i = i - 1
' If Range(Cells(i, 1), Cells(i, 8)).Interior.Color = RGB(0, 255, 0) Or Cells(i, 5).Value = "" Or Cells(i, 6).Value = "" Or Cells(i, 7).Value = "-" Or Cells(i, 4).Value = "Value" Then
'
' End If
j = Cells(i, 1).Value
If j = "time" Then Exit Sub
If j = 0 Then Exit Sub
Next i
'XXXXXXXXXXXXXXXXXXXXProgramXXendsXXXXXXXXXXXXXXXXXXXXXXXXX
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
|