AW: EDI (Txt) Datei mit Excel einlesen
01.08.2013 12:10:10
Rudi
Hallo,
in ein Modul:
Option Explicit
Sub ReadEDI()
Dim sFile As String, arrFile, i As Long, arrDaten(), iCounter As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Datei wählen"
If .Show = -1 Then
sFile = .SelectedItems(1)
End If
End With
If sFile "" Then
Open sFile For Input As #1
arrFile = Split(Input(LOF(1), 1), vbCrLf)
Close #1
ReDim arrDaten(UBound(arrFile))
For i = 0 To UBound(arrFile)
Select Case Left(arrFile(i), 2)
Case "R0"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 5, 7, 10, 15, 23, 58), _
Array(2, 2, 3, 5, 8, 35, 30))
Case "R1"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 7, 13, 19, 25, 31, 56, 81, 84, 114, 117, 148, 154, 165, 177, 186, 199), _
Array(4, 6, 6, 6, 6, 25, 25, 3, 30, 3, 31, 6, 11, 12, 9, 13, 3))
Case "R2"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 6, 56, 69), _
Array(3, 50, 13, 3))
Case "R3"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 33, 38, 41, 71, 116), _
Array(30, 5, 3, 30, 45, 11))
Case "R4"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3), _
Array(75))
Case "R6"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 4, 39, 74, 109, 144, 153, 179, 181), _
Array(1, 35, 35, 35, 35, 9, 26, 2, 3))
Case "RT"
arrDaten(i) = fncEDI(arrFile(i), _
Array(3, 6, 12), _
Array(3, 6, 11))
End Select
Next
End If
With Worksheets.Add
For i = 0 To UBound(arrDaten)
If IsArray(arrDaten(i)) Then
.Cells(i + 1, 1).Resize(, UBound(arrDaten(i)) + 1) = arrDaten(i)
End If
Next
.Columns.AutoFit
End With
End Sub
Function fncEDI(ByVal strTmp As String, arr1, arr2)
Dim arrTmp(), i As Integer
ReDim arrTmp(UBound(arr1))
For i = 0 To UBound(arr1)
arrTmp(i) = Trim(Mid(strTmp, arr1(i), arr2(i)))
Next
fncEDI = arrTmp
End Function
Gruß
Rudi