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
| Option Explicit
Sub IV_CEA()
Dim WB As Workbook, TreatedFile As Workbook
Dim runnumber As String, OpenFile As String, dl As String, dc As String, area As String, referencearea As String
Dim sign As String, resign As String
Dim chemin_fichier_traite As String, chemin_enregistrement As String, fichier_traite As String
Dim i As Integer
Application.ScreenUpdating = False
Set WB = ThisWorkbook
OpenFile = Application.GetOpenFilename(",*.txt", , "IV File Selection", , False)
If VarType(OpenFile) = vbBoolean Then Exit Sub
Set TreatedFile = Application.Workbooks.Open(OpenFile, xlMSDOS)
fichier_traite = TreatedFile.Name
chemin_fichier_traite = TreatedFile.Path
With TreatedFile
dl = Worksheets(1).Range("A1000").End(xlUp).Row 'cherche le nombre de ligne
dc = Worksheets(1).Range("Z1").End(xlToLeft).Column 'cherche le nombre de colonne
Worksheets(1).Range(Cells(1, 1), Cells(CInt(dl), CInt(dc))).Copy WB.Worksheets(2).Cells(1, 1)
i = 2
For i = 2 To CInt(dl)
If Cells(i, 3).Value = Empty Then
Range(Cells(i, 4), Cells(i, CInt(dc))).Copy WB.Worksheets(2).Cells(i, 3)
End If
Next i
.Close (False)
End With
With WB.Worksheets(2)
runnumber = WB.Worksheets(2).Cells(2, 1).Value
referencearea = WB.Worksheets(2).Cells(2, 3).Value
area = WB.Worksheets(2).Cells(3, 3).Value
Range(Cells(2, 2), Cells(CInt(dl), CInt(dc))).Sort Key1:=Cells(2, 2), Order1:=xlAscending, Header:=xlGuess
.Range("K:L").Cut WB.Worksheets(2).Cells(1, 19)
.Range("N:N").Cut WB.Worksheets(2).Cells(1, 21)
.Range("A:A, C:G, J:L, N:N, P:Q").Delete
.Rows(1).Font.Bold = True
End With
End Sub |
Partager