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 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
|
Sub Contact_Resistance()
Dim WB As Workbook, TreatedFile As Workbook
Dim Counter As Variant
Dim a As Integer, dl As String, i As Integer, j As Integer, l As Integer
Dim Rcontact() As Variant
Dim Deviation() As Variant
Dim tableau As Variant
Dim moyenne As Variant
Dim dev As String
Dim chemin As String
Dim run As String
Dim sheetreconnaissance As String
Dim separationcharacter As String
Application.ScreenUpdating = False
run = InputBox("Run Number?")
If run = "" Then Exit Sub
separationcharacter = InputBox("Enter your separation character:")
If VarType(Counter) = vbBoolean Then GoTo suite
suite:
Set WB = ThisWorkbook
Counter = Application.GetOpenFilename("*,*", , , , True) 'Ouverture de la boite de dialogue pour selection des fichiers .txt
If VarType(Counter) = vbBoolean Then Exit Sub
j = 4
l = 4
For a = 1 To UBound(Counter)
Set TreatedFile = Application.Workbooks.Open(Counter(a), xlMDOS)
dl = Range("A65536").End(xlUp).Row - 2
i = 0
ReDim Rcontact(dl)
ReDim Deviation(dl)
tableau = Split(TreatedFile.Name, separationcharacter)
nom = tableau(0)
For i = 0 To dl - 1
Rcontact(i) = TreatedFile.Worksheets(1).Range("G" & i + 3).Value
Deviation(i) = TreatedFile.Worksheets(1).Range("H" & i + 3).Value
moyenne = Application.WorksheetFunction.Average(Range("G3:G" & i + 3))
dev = Application.WorksheetFunction.StDev(Range(Cells(3, 7), Cells(i + 3, 7)))
If UBound(tableau) > 0 Then
If a Mod 2 = 0 Then
WB.Worksheets(1).Cells(l + 1, i + 8).Value = Rcontact(i)
WB.Worksheets(1).Cells(l + 2, i + 8).Value = Deviation(i)
If i = dl - 1 Then l = l + 5
Else
WB.Worksheets(1).Cells(j + 1, i + 2).Value = Rcontact(i)
WB.Worksheets(1).Cells(j + 2, i + 2).Value = Deviation(i)
If i = dl - 1 Then
WB.Worksheets(1).Cells(j, 1).Value = nom
WB.Worksheets(1).Cells(j + 3, 2).Value = moyenne
'WB.Worksheets(1).Cells(j + 3, 3).Value = bla
WB.Worksheets(1).Cells(j + 1, 1).Value = "RContact (mOhms.cm2)"
WB.Worksheets(1).Cells(j + 1, 1).Characters(2, 7).Font.Subscript = True
WB.Worksheets(1).Cells(j + 1, 1).Characters(19, 1).Font.Superscript = True
WB.Worksheets(1).Cells(j + 2, 1).Value = "Individual Deviation"
WB.Worksheets(1).Cells(j + 3, 1).Value = "RContact Average & Deviation"
WB.Worksheets(1).Cells(j + 3, 1).Characters(2, 7).Font.Subscript = True
'WB.Worksheets(1).Range(.Cells(3, 2), .Cells(3, dl + 1)).Font.Bold = True
j = j + 5
End If
End If
Else
WB.Worksheets(1).Cells(j + 1, i + 2).Value = Rcontact(i)
WB.Worksheets(1).Cells(j + 2, i + 2).Value = Deviation(i)
If i = dl - 1 Then
WB.Worksheets(1).Cells(j, 1).Value = Split(TreatedFile.Name, ".")
WB.Worksheets(1).Cells(j + 3, 2).Value = moyenne
'WB.Worksheets(1).Cells(j + 3, 3).Value = dev
WB.Worksheets(1).Cells(j + 1, 1).Value = "RContact (mOhms.cm2)"
WB.Worksheets(1).Cells(j + 1, 1).Characters(2, 7).Font.Subscript = True
WB.Worksheets(1).Cells(j + 1, 1).Characters(19, 1).Font.Superscript = True
WB.Worksheets(1).Cells(j + 2, 1).Value = "Individual Deviation"
WB.Worksheets(1).Cells(j + 3, 1).Value = "RContact Average & Deviation"
WB.Worksheets(1).Cells(j + 3, 1).Characters(2, 7).Font.Subscript = True
'WB.Worksheets(1).Range(.Cells(3, 2), .Cells(3, dl + 1)).Font.Bold = True
' .Range(.Cells(j, 1), .Cells(j, 1)).Interior.ColorIndex = 15
'.Range(.Cells(j + 3, 1), .Cells(j + 3, 1)).Interior.ColorIndex = 15
j = j + 5
End If
End If
Next i
TreatedFile.Close
Next a 'fin de la boucle
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
With WB
.Worksheets(1).Cells.NumberFormat = "0.000"
.Worksheets(1).Columns.AutoFit
.Worksheets(1).Rows(3).RowHeight = 20
End With
chemin = Application.GetSaveAsFilename("Contact Resistance_Resume Run " & run, ", *.xls")
WB.SaveAs (chemin)
Set WB = Nothing
Set TreatedFile = Nothing
End Sub |
Partager