|
Moin
ich habe vor , aus einer bestehenden ExcelDatei eine csv zu kreieren. Das ist bisher auch ganz gut gelungen.
Problem ist jetzt, dass die csv in Zeile 1 mit der Kopfzeile beginnt. Das Ganze soll aber erst in Zeile 9 beginnen
Danke schon einmal an Euch
Sub ExportCSV()
Dim Blatt As Worksheet, Jahr As Long, BeginnSZ As Date, BeginnWZ As Date, sz As Boolean
Dim Pfad As String, Zeitstrng As String, Datum As Date, Zeit As Date, p As Boolean, Zeitbis As Date
Set Blatt = Sheets("Gas_Plan")
Pfad1 = "O:\EZ Strom\Gasplanung\Temp" & "\" & Format(Worksheets("Gas_Plan").Range("A4"), "YYYY_MM_DD") & "_" & "Gasfahrplan_Kraftwerk" & ".csv"
Pfad2 = "O:\EZ Strom\Gasplanung\Temp" & "\" & Format(Worksheets("Gas_Plan").Range("A5"), "YYYY_MM_DD") & "_" & "Gasfahrplan_Kraftwerk" & ".csv"
Pfad3 = "O:\EZ Strom\Gasplanung\Temp" & "\" & Format(Worksheets("Gas_Plan").Range("A6"), "YYYY_MM_DD") & "_" & "Gasfahrplan_Kraftwerk" & ".csv"
Pfad4 = "O:\EZ Strom\Gasplanung\Temp" & "\" & Format(Worksheets("Gas_Plan").Range("A7"), "YYYY_MM_DD") & "_" & "Gasfahrplan_Kraftwerk" & ".csv"
Pfad5 = "O:\EZ Strom\Gasplanung\Temp" & "\" & Format(Worksheets("Gas_Plan").Range("A8"), "YYYY_MM_DD") & "_" & "Gasfahrplan_Kraftwerk" & ".csv"
Dim fs As Object, ordner As Object
Set fs = CreateObject("Scripting.filesystemobject")
Set ordner = fs.getfolder(Worksheets("Test").Cells(10, 1).Value)
If ordner.subfolders.Count * 1 + ordner.Files.Count * 1 = 1 Then
MsgBox ("Ordner enthält Daten! Die vorhandenen Dateien werden jetzt automatisch gelöscht!"), vbYes
If vbYes Then
'löscht alle .csv im Verzeichnis
Const strVerz As String = "O:\EZ Strom\Gasplanung\Temp\"
Kill strVerz & "*.*"
End If
End If
If ActiveSheet.Range("A4").Value <> "" Then
lzei = Cells(Rows.Count, 1).End(xlUp).Row
Open Pfad1 For Output As #1
Print #1, "Datum;von;bis;Gas_Plan_Kraftwerk"
For Z = 4 To 4
For Y = 3 To 3
Datum = Blatt.Cells(Z, 1)
Zeitbis = Blatt.Cells(Y, 3)
Jahr = Year(Datum)
BeginnSZ = DateSerial(Jahr, 3, 31) - Weekday(DateSerial(Jahr, 3, 31)) + 1
BeginnWZ = DateSerial(Jahr, 10, 31) - Weekday(DateSerial(Jahr, 10, 31)) + 1
For s = 3 To 26
'If (Datum = BeginnSZ And s = 10) Or (Datum = BeginnWZ And s = 14) Then Stop
Zeit = Blatt.Cells(2, s)
If Datum = BeginnSZ And CStr(Zeit) = "02:00:00" Then
s = s + 4
Zeit = Blatt.Cells(2, s)
ElseIf Datum = BeginnWZ And CStr(Zeit) = "03:00:00" And p = False Then
s = s - 4
Zeit = Blatt.Cells(2, s)
p = True
End If
Zeitbis = Blatt.Cells(3, s)
If Datum + Zeit >= BeginnSZ + TimeValue("03:00:00") And Datum + Zeit < BeginnWZ + TimeValue("03:00:00") And p = False Then sz = True Else sz = False
Print #1, Format(Datum + Zeit, "yyyy-mm-dd ") & ";" & Format(Zeit, "hh:nn:ss") & ";" & Format(Zeitbis, "hh:nn:ss") & ";" & Cells(Z, s)
Next s
Next Y
Next Z
Close #1
End If
'*************************************************************
If ActiveSheet.Range("A5").Value <> "" Then
lzei = Cells(Rows.Count, 1).End(xlUp).Row
Open Pfad2 For Output As #2
Print #2, "Datum;von;bis;Gas_Plan_Kraftwerk"
For Z = 5 To 5
For Y = 3 To 3
Datum = Blatt.Cells(Z, 1)
Zeitbis = Blatt.Cells(Y, 3)
Jahr = Year(Datum)
BeginnSZ = DateSerial(Jahr, 3, 31) - Weekday(DateSerial(Jahr, 3, 31)) + 1
BeginnWZ = DateSerial(Jahr, 10, 31) - Weekday(DateSerial(Jahr, 10, 31)) + 1
For s = 3 To 26
'If (Datum = BeginnSZ And s = 10) Or (Datum = BeginnWZ And s = 14) Then Stop
Zeit = Blatt.Cells(2, s)
If Datum = BeginnSZ And CStr(Zeit) = "02:00:00" Then
s = s + 4
Zeit = Blatt.Cells(2, s)
ElseIf Datum = BeginnWZ And CStr(Zeit) = "03:00:00" And p = False Then
s = s - 4
Zeit = Blatt.Cells(2, s)
p = True
End If
Zeitbis = Blatt.Cells(3, s)
If Datum + Zeit >= BeginnSZ + TimeValue("03:00:00") And Datum + Zeit < BeginnWZ + TimeValue("03:00:00") And p = False Then sz = True Else sz = False
Print #2, Format(Datum + Zeit, "yyyy-mm-dd hh:nn:ss") & ";" & Format(Zeit, "hh:nn:ss") & ";" & Format(Zeitbis, "hh:nn:ss") & ";" & Cells(Z, s)
Next s
Next Y
Next Z
Close #2
End If
'*************************************************************
If ActiveSheet.Range("A6").Value <> "" Then
lzei = Cells(Rows.Count, 1).End(xlUp).Row
Open Pfad3 For Output As #3
Print #3, "Datum;von;bis;Gas_Plan_Kraftwerk"
For Z = 6 To 6
For Y = 3 To 3
Datum = Blatt.Cells(Z, 1)
Zeitbis = Blatt.Cells(Y, 3)
Jahr = Year(Datum)
BeginnSZ = DateSerial(Jahr, 3, 31) - Weekday(DateSerial(Jahr, 3, 31)) + 1
BeginnWZ = DateSerial(Jahr, 10, 31) - Weekday(DateSerial(Jahr, 10, 31)) + 1
For s = 3 To 26
'If (Datum = BeginnSZ And s = 10) Or (Datum = BeginnWZ And s = 14) Then Stop
Zeit = Blatt.Cells(2, s)
If Datum = BeginnSZ And CStr(Zeit) = "02:00:00" Then
s = s + 4
Zeit = Blatt.Cells(2, s)
ElseIf Datum = BeginnWZ And CStr(Zeit) = "03:00:00" And p = False Then
s = s - 4
Zeit = Blatt.Cells(2, s)
p = True
End If
Zeitbis = Blatt.Cells(3, s)
If Datum + Zeit >= BeginnSZ + TimeValue("03:00:00") And Datum + Zeit < BeginnWZ + TimeValue("03:00:00") And p = False Then sz = True Else sz = False
Print #3, Format(Datum + Zeit, "yyyy-mm-dd hh:nn:ss") & ";" & Format(Zeit, "hh:nn:ss") & ";" & Format(Zeitbis, "hh:nn:ss") & ";" & Cells(Z, s)
Next s
Next Y
Next Z
Close #3
End If
'*************************************************************
If ActiveSheet.Range("A7").Value <> "" Then
lzei = Cells(Rows.Count, 1).End(xlUp).Row
Open Pfad4 For Output As #4
Print #4, "Datum;von;bis;Gas_Plan_Kraftwerk"
For Z = 7 To 7
For Y = 3 To 3
Datum = Blatt.Cells(Z, 1)
Zeitbis = Blatt.Cells(Y, 3)
Jahr = Year(Datum)
BeginnSZ = DateSerial(Jahr, 3, 31) - Weekday(DateSerial(Jahr, 3, 31)) + 1
BeginnWZ = DateSerial(Jahr, 10, 31) - Weekday(DateSerial(Jahr, 10, 31)) + 1
For s = 3 To 26
'If (Datum = BeginnSZ And s = 10) Or (Datum = BeginnWZ And s = 14) Then Stop
Zeit = Blatt.Cells(2, s)
If Datum = BeginnSZ And CStr(Zeit) = "02:00:00" Then
s = s + 4
Zeit = Blatt.Cells(2, s)
ElseIf Datum = BeginnWZ And CStr(Zeit) = "03:00:00" And p = False Then
s = s - 4
Zeit = Blatt.Cells(2, s)
p = True
End If
Zeitbis = Blatt.Cells(3, s)
If Datum + Zeit >= BeginnSZ + TimeValue("03:00:00") And Datum + Zeit < BeginnWZ + TimeValue("03:00:00") And p = False Then sz = True Else sz = False
Print #4, Format(Datum + Zeit, "yyyy-mm-dd hh:nn:ss") & ";" & Format(Zeit, "hh:nn:ss") & ";" & Format(Zeitbis, "hh:nn:ss") & ";" & Cells(Z, s)
Next s
Next Y
Next Z
Close #4
End If
'*************************************************************
If ActiveSheet.Range("A8").Value <> "" Then
lzei = Cells(Rows.Count, 1).End(xlUp).Row
Open Pfad5 For Output As #5
Print #5, "Datum;von;bis;Gas_Plan_Kraftwerk"
For Z = 8 To 8
For Y = 3 To 3
Datum = Blatt.Cells(Z, 1)
Zeitbis = Blatt.Cells(Y, 3)
Jahr = Year(Datum)
BeginnSZ = DateSerial(Jahr, 3, 31) - Weekday(DateSerial(Jahr, 3, 31)) + 1
BeginnWZ = DateSerial(Jahr, 10, 31) - Weekday(DateSerial(Jahr, 10, 31)) + 1
For s = 3 To 26
'If (Datum = BeginnSZ And s = 10) Or (Datum = BeginnWZ And s = 14) Then Stop
Zeit = Blatt.Cells(2, s)
If Datum = BeginnSZ And CStr(Zeit) = "02:00:00" Then
s = s + 4
Zeit = Blatt.Cells(2, s)
ElseIf Datum = BeginnWZ And CStr(Zeit) = "03:00:00" And p = False Then
s = s - 4
Zeit = Blatt.Cells(2, s)
p = True
End If
Zeitbis = Blatt.Cells(3, s)
If Datum + Zeit >= BeginnSZ + TimeValue("03:00:00") And Datum + Zeit < BeginnWZ + TimeValue("03:00:00") And p = False Then sz = True Else sz = False
Print #5, Format(Datum + Zeit, "yyyy-mm-dd hh:nn:ss") & ";" & Format(Zeit, "hh:nn:ss") & ";" & Format(Zeitbis, "hh:nn:ss") & ";" & Cells(Z, s)
Next s
Next Y
Next Z
Close #5
End If
MsgBox "Die Datei wurde erzeugt."
End Sub
|