Ein Beispiel Skript für das Umwandeln von Excel Tabellen nach csv-Dateien:
Sub csvToExcelTabelle()
ExcSheetName = "yyy.xls"
Csvsheet = "xxx.csv"
Dim excl As Worksheet
Dim csv As Worksheet
Set csv = Workbooks(Csvsheet).Worksheets(1)
j = 1
'csv leeren
csv.Cells.Clear
For Each excl In Workbooks(ExcSheetName).Worksheets
zeilen = excl.UsedRange.Rows.Count
For i = 3 To zeilen
'Zahlenformate anpassen
excl.Cells(i, 13).NumberFormat = "m/d/yyyy"
excl.Cells(i, 14).NumberFormat = "m/d/yyyy"
excl.Cells(i, 2).NumberFormat = "h:mm;@"
excl.Cells(i, 3).NumberFormat = "h:mm;@"
csv.Cells(j, 1).Value = "D"
csv.Cells(j, 2).Value = "NO"
'DATEFROM
DATEFROM = excl.Cells(i, 13).Value
csv.Cells(j, 3).Value = DATEFROM
'DAYOFPERIOD
DAYOFPERIOD = ""
For k = 1 To 7
If (excl.Cells(i, 3 + k).Value = "x") Then
DAYOFPERIOD = DAYOFPERIOD & k
End If
Next k
csv.Cells(j, 5).Value = DAYOFPERIOD
'SCHEDULEDTIME
SCHEDULEDTIME = excl.Cells(i, 2).Value
csv.Cells(j, 6).Value = Format(SCHEDULEDTIME, "h:mm;@")
'REMOTE_ und TONAME
REMOTE__tmp = excl.Cells(i, 1).Value
pos = InStr(REMOTE__tmp, " (")
If (pos <> 0) Then
REMOTE_ = Mid(REMOTE__tmp, pos + 2, Len(REMOTE__tmp) - pos - 2)
TONAME = Left(REMOTE__tmp, pos - 1)
Else
REMOTE_ = ""
TONAME = ""
End If
csv.Cells(j, 7).Value = REMOTE_
csv.Cells(j, 8).Value = TONAME
'VIA1
VIA1 = excl.Cells(i, 12).Value
csv.Cells(j, 9).Value = VIA1
j = j + 1
Next
Next excl
End Sub