1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
| Sub MeF_ProduitsComposes()
On Error GoTo Err_ProduitsComposes
Dim CtFic As String
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
CtFic = RepExp & "\" & FicExp
If Dir(CtFic) <> "" Then
If fs.FileExists(CtFic) = True Then
Kill CtFic
End If
End If
ChDir RepImp
Workbooks.OpenText Filename:= _
RepImp & "\" & FicImp _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=True, OtherChar:="#", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array _
(6, 1), Array(7, 1)), DecimalSeparator:=".", TrailingMinusNumbers:=True
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
RepExp & "\" & FicExp _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("C1").Select
ActiveCell.FormulaR1C1 = "Compose"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Libelle Compose"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Composant"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Libelle Composant"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
i = 2
While Cells(i, 5) <> ""
If Cells(i, 3) = "" Then
Range(Cells(i - 1, 1), Cells(i - 1, 4)).Select
Selection.Copy
Cells(i, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
i = i + 1
Wend
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
Windows("MiseEnForme.xls").Activate
Sheets("Macros").Select
Cells(1, 3) = "Ok"
Fin_ProduitsComposes:
Exit Sub
Err_ProduitsComposes:
Cells(1, 3) = "NonOk"
GoTo Fin_ProduitsComposes
End Sub |
Partager