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
|
Sub MeF_ProduitsComposes()
On Error GoTo Err_ProduitsComposes
Dim RepFic As String
Dim NomFic As String
RepFic = "Q:\Informatique\Commun\09 - Gestion Commerciale\01-Requetes\FichiersBase\PCS\"
NomFic = "ProduitsComposes"
If Dir(RepFic & NomFic & ".xls") <> "" Then
Kill RepFic & NomFic & ".xls"
End If
ChDir RepFic
Workbooks.OpenText Filename:= _
RepFic & NomFic & ".txt" _
, 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
ActiveWorkbook.SaveAs Filename:= _
RepFic & NomFic & ".xls" _
, 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
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