Public nomfich1 As String
Type interface
int1 As String * 8
int2 As String * 4
int3 As String * 4
int4 As String * 1
int5 As String * 7
int6 As String * 2
int7 As String * 1
int8 As String * 112
int9 As String * 60
int10 As String * 20
int11 As String * 12
int12 As String * 12
int13 As String * 12
int14 As String * 3
int15 As String * 1
int16 As String * 2
int17 As String * 7
int18 As String * 7
int19 As String * 5
int20 As String * 5
int21 As String * 11
int22 As String * 2
int23 As String * 5
int24 As String * 30
int25 As String * 30
int26 As String * 30
int27 As String * 30
int28 As String * 5
int29 As String * 30
int30 As String * 4
int31 As String * 10
int32 As String * 1
int33 As String * 10
int34 As String * 3
int35 As String * 1
int36 As String * 2
End Type
Public Function ExisteFichier(S As String) As Boolean
Dim francis As Object
Set francis = CreateObject("Scripting.FileSystemObject")
ExisteFichier = francis.FileExists(S)
End Function
Sub auto_open()
Set FSys = CreateObject("Scripting.FileSystemObject")
'Set MonFic = FSys.CreateTextFile("C:\DOCUME~1\FRVARA~1\Bureau\WORK_T~1\COMPTA~2\cibles\infodb.dat")
Set MonFic = FSys.CreateTextFile("\\srvB\applicatifs$\Perc\INTG_GA\bon\INTEG\cibles\infodb.dat")
End Sub
Sub IntText()
Dim MyInterface As interface
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim nbcount0 As Integer
' selection du fichier à modifier
Worksheets("import").Activate
Cells.Select
Selection.ClearContents
Range("A1").Select
ChDrive "F:\"
ChDir "\\srvB\applicatifs$\Perc\INTG_GA\bon\INTEG\"
MsgBox ("Sélection du fichier à transformer")
nomfich1 = Application.GetOpenFilename("Tous les fichiers,*.dat")
' lecture fichier et repart dans les variables
i = 1
Open nomfich1 For Binary As #1 Len = Len(MyInterface)
'Open "C:\DOCUME~1\FRVARA~1\Bureau\WORK_T~1\COMPTA~2\TEST_O~1\test.dat" For Binary As #1 Len = Len(MyInterface)
'Open "Y:\Bureau\WORK_T~1\COMPTA~2\TEST_O~1\test.dat" For Binary As #1 Len = Len(MyInterface)
Do While Not EOF(1)
Get #1, , MyInterface
Cells(i, 1) = MyInterface.int1
Cells(i, 2) = MyInterface.int2
Cells(i, 3) = MyInterface.int3
Cells(i, 4) = MyInterface.int4
Cells(i, 5) = MyInterface.int5
Cells(i, 6) = MyInterface.int6
Cells(i, 7) = MyInterface.int7
Cells(i, 8) = MyInterface.int8
Cells(i, 9) = MyInterface.int9
Cells(i, 10) = MyInterface.int10
Cells(i, 11) = MyInterface.int11
Cells(i, 12) = MyInterface.int12
Cells(i, 13) = MyInterface.int13
Cells(i, 14) = MyInterface.int14
Cells(i, 15) = MyInterface.int15
Cells(i, 16) = MyInterface.int16
Cells(i, 17) = MyInterface.int17
Cells(i, 18) = MyInterface.int18
Cells(i, 19) = MyInterface.int19
Cells(i, 20) = MyInterface.int20
Cells(i, 21) = MyInterface.int21
Cells(i, 22) = MyInterface.int22
Cells(i, 23) = MyInterface.int23
Cells(i, 24) = MyInterface.int24
Cells(i, 25) = MyInterface.int25
Cells(i, 26) = MyInterface.int26
Cells(i, 27) = MyInterface.int27
Cells(i, 28) = MyInterface.int28
Cells(i, 29) = MyInterface.int29
Cells(i, 30) = MyInterface.int30
Cells(i, 31) = MyInterface.int31
Cells(i, 32) = MyInterface.int32
Cells(i, 33) = MyInterface.int33
Cells(i, 34) = MyInterface.int34
Cells(i, 35) = MyInterface.int35
Cells(i, 36) = MyInterface.int36
i = i + 1
Loop
Close #1
' traitement des données parasites (Le Directeur)
' colonne 24
Worksheets("import").Activate
nbcount0 = (Application.CountA(Range("A1:A65536"))) - 1
For i = 1 To nbcount0
If Cells(i, 24) = "Le directeur " Then
Cells(i, 24) = " "
Else
'MsgBox Len(Cells(i, 24))
Cells(i, 24) = Cells(i, 24)
End If
Next i
' colonne 25
For k = 1 To nbcount0
If Cells(k, 25) = "Le directeur " Then
Cells(k, 25) = " "
Else
Cells(k, 25) = Cells(k, 25)
End If
Next k
' traitement des données parasites (numéro tiers destinataire)
Cells(1, 26).Activate
Selection.EntireColumn.Insert
Columns("Z").Select
Selection.NumberFormat = "General"
For i = 1 To nbcount0
Cells(i, 26).Activate
'ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(FIND("")"",R" & i & "C25,1)+1)-(FIND(""("",R" & i & "C25,1)-1)),""""),"""")),(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(LEN(R" & i & "C25)+1)-(FIND(""("",R" & i & "C25,1)-1)),""""),"""")),(IF(ISERROR(FIND(""("",R" & i & "C25,1)),R" & i & "C25,(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(FIND("")"",R" & i & "C25,1)+1)-(FIND(""("",R" & i & "C25,1)-1)),""""),"""")))))"
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(FIND("")"",R" & i & "C25,1)+1)-(FIND(""("",R" & i & "C25,1)-1)),"" ""),"" "")),(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(LEN(R" & i & "C25)+1)-(FIND(""("",R" & i & "C25,1)-1)),"" ""),"" "")),(IF(ISERROR(FIND(""("",R" & i & "C25,1)),R" & i & "C25,(IF(ISNUMBER(FIND(""("",R" & i & "C25,1)-1),SUBSTITUTE(R" & i & "C25,MID(R" & i & "C25,FIND(""("",R" & i & "C25,1)-1,(FIND("")"",R" & i & "C25,1)+1)-(FIND(""("",R" & i & "C25,1)-1)),"" ""),"" "")))))"
Next i
For j = 1 To nbcount0
Columns("Y").Select
Selection.NumberFormat = "General"
Cells(j, 25) = Cells(j, 26).Value
Next j
Columns("Z").Select
Selection.Delete
Cells(1, 1).Activate
Call IntText2
End Sub