Degerli arkadaslar asagida ki macro ile sunu yapmak istiyorum:
Elimde bir text dosyasi var bunu excele dogru kolonlara okutmak istiyom. Suanda asagidaki macroyu calistirdigimda excel de su goruntuyu veriyor:
Elimdeki text doasyasindaki bilgiler asagida gorunuyor burada surekli veriler kendini tekrarliyor:
Yapmak istedigim excel calisma dosyasinda kolom A: icine AUTCF, B: O'Flarerty, C. (Colm), C: AUTHORISATIE-TECHNBECH ve D kolomundan N koloma kadar ise Asigned unit in karsindaki verileri sirasiyla excel calisma dosyasina almak istiyorum.
Asagidaki macroda hangi hata veya eksilik bulunuyor cozmedim lutfen yardimci ourmusunuz.
Cok tesekkurederim simdiden!
Operator ID = AUTCF
Name = O'Flaherty, C. (Colm)
Active profile = AUTORISATIE-TECHNBEH
Enable status = Enabled
Re-enable date = n/a
Approval status = Approved
Last changed = 14/02/08 09:08:54
Last sign-on = n/a
Calculated pwd = 07
One-time password = No
Assigned unit = None
Assigned unit = BTR
Assigned unit = BUBV
Assigned unit = DOC
Assigned unit = EFF
Assigned unit = OPER
Assigned unit = RECLAME
Assigned unit = SEC
Assigned unit = BIBV
Assigned unit = SAM
Sub GRIB()
Dim Bestand As String
Dim Inlees As String
Dim i, u, a As Integer
Close #1
On Error GoTo Einde
Bestand = "C:\Documents and Settings\OG\Mijn documenten\deze\SWIFT1.TXT"
Eerste = True
i = 1
'kolomkoppen
Range("A" & i).Value = "Operator ID"
Range("B" & i).Value = "Name"
Range("C" & i).Value = "Profile"
For a = 1 To 10
Cells(1, a + 3) = "Assigned_unit" & a
Next a
'Bestand inlezen
Open Bestand For Input As #1 ' Open file.
While Not EOF(1) ' Loop until end of file.
'
'
Volgende:
Line Input #1, Inlees
'Zoeken naar juiste waarde voor Operator ID
Select Case Len(Inlees)
Case Is > 0
Select Case Left(Inlees, 11)
Case "Operator ID"
i = i + 1
Cells(i, 1) = RTrim(Mid(Inlees, 25, 6))
' Volgende 2 regels inlezen en weggooien
Line Input #1, Inlees
Line Input #1, Inlees
Cells(i, 2) = RTrim(Mid(Inlees, 25, 30))
' Volgende 7 regels inlezen en weggooien
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
'Loop voor doorlopen van Assigned unit
For u = 1 To 10 'Maximaal 10 Assigned units
Line Input #1, Inlees
Select Case Left(Inlees, 13)
Case "Assigned unit"
Cells(i, 2 + u) = RTrim(Mid(Inlees, 25, 15))
Case Else
GoTo Volgende
End Select
Next u
End Select
End Select
Wend
Einde:
MsgBox "Klaar met inlezen"
Close #1 ' Close file.
End Sub
Elimde bir text dosyasi var bunu excele dogru kolonlara okutmak istiyom. Suanda asagidaki macroyu calistirdigimda excel de su goruntuyu veriyor:
Elimdeki text doasyasindaki bilgiler asagida gorunuyor burada surekli veriler kendini tekrarliyor:
Yapmak istedigim excel calisma dosyasinda kolom A: icine AUTCF, B: O'Flarerty, C. (Colm), C: AUTHORISATIE-TECHNBECH ve D kolomundan N koloma kadar ise Asigned unit in karsindaki verileri sirasiyla excel calisma dosyasina almak istiyorum.
Asagidaki macroda hangi hata veya eksilik bulunuyor cozmedim lutfen yardimci ourmusunuz.
Cok tesekkurederim simdiden!
Operator ID = AUTCF
Name = O'Flaherty, C. (Colm)
Active profile = AUTORISATIE-TECHNBEH
Enable status = Enabled
Re-enable date = n/a
Approval status = Approved
Last changed = 14/02/08 09:08:54
Last sign-on = n/a
Calculated pwd = 07
One-time password = No
Assigned unit = None
Assigned unit = BTR
Assigned unit = BUBV
Assigned unit = DOC
Assigned unit = EFF
Assigned unit = OPER
Assigned unit = RECLAME
Assigned unit = SEC
Assigned unit = BIBV
Assigned unit = SAM
Sub GRIB()
Dim Bestand As String
Dim Inlees As String
Dim i, u, a As Integer
Close #1
On Error GoTo Einde
Bestand = "C:\Documents and Settings\OG\Mijn documenten\deze\SWIFT1.TXT"
Eerste = True
i = 1
'kolomkoppen
Range("A" & i).Value = "Operator ID"
Range("B" & i).Value = "Name"
Range("C" & i).Value = "Profile"
For a = 1 To 10
Cells(1, a + 3) = "Assigned_unit" & a
Next a
'Bestand inlezen
Open Bestand For Input As #1 ' Open file.
While Not EOF(1) ' Loop until end of file.
'
'
Volgende:
Line Input #1, Inlees
'Zoeken naar juiste waarde voor Operator ID
Select Case Len(Inlees)
Case Is > 0
Select Case Left(Inlees, 11)
Case "Operator ID"
i = i + 1
Cells(i, 1) = RTrim(Mid(Inlees, 25, 6))
' Volgende 2 regels inlezen en weggooien
Line Input #1, Inlees
Line Input #1, Inlees
Cells(i, 2) = RTrim(Mid(Inlees, 25, 30))
' Volgende 7 regels inlezen en weggooien
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
Line Input #1, weg
'Loop voor doorlopen van Assigned unit
For u = 1 To 10 'Maximaal 10 Assigned units
Line Input #1, Inlees
Select Case Left(Inlees, 13)
Case "Assigned unit"
Cells(i, 2 + u) = RTrim(Mid(Inlees, 25, 15))
Case Else
GoTo Volgende
End Select
Next u
End Select
End Select
Wend
Einde:
MsgBox "Klaar met inlezen"
Close #1 ' Close file.
End Sub
Son düzenleme: