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 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
| Sub M2()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\Users\t2000107\Desktop\Datas2.xlsx"
Workbooks.Open "C:\Users\t2000107\Desktop\Datas2.xlsx"
Workbooks("Datas2.xlsx").Worksheets("Sheet1").Name = "Datas"
Dim I As Long
For I = 716001 To 716016
Workbooks.Open "C:\Users\t2000107\Desktop\Bureau\Experiment data\20190716_001_xf16.2m_Z100mm\" & I & ".csv"
Sheets("" & I&).Range("A52:C10014").Copy
Workbooks("Datas2.xlsx").Activate
Sheets("Datas").Select
Sheets("Datas").Cells(1, 1 + 4 * (I - 716001)).PasteSpecial xlPasteAll
Next I
Workbooks("Datas2.xlsx").Save
Dim Wbk As Workbook
For Each Wbk In Workbooks
If Wbk.Name <> ThisWorkbook.Name Then
Wbk.Close
End If
Next Wbk
Workbooks.Open "C:\Users\t2000107\Desktop\Datas2.xlsx"
ActiveWorkbook.Sheets.Add After:=Worksheets(1)
Workbooks("Datas2.xlsx").Worksheets("Sheet1").Name = "Time&AV"
Sheets("Time&AV").Range("A1") = "Time (s)"
Sheets("Time&AV").Range("B1") = "Average Velocity (m/s)"
Sheets("Time&AV").Range("A2") = 360
Sheets("Time&AV").Range("A3") = 380
Sheets("Time&AV").Range("A4") = 400
Sheets("Time&AV").Range("A5") = 420
Sheets("Time&AV").Range("A6") = 440
Sheets("Time&AV").Range("A7") = 460
Sheets("Time&AV").Range("A8") = 480
Sheets("Time&AV").Range("A9") = 500
Sheets("Time&AV").Range("A10") = 520
Sheets("Time&AV").Range("A11") = 540
Sheets("Time&AV").Range("A12") = 560
Sheets("Time&AV").Range("A13") = 580
Sheets("Time&AV").Range("A14") = 600
Sheets("Time&AV").Range("A15") = 620
Sheets("Time&AV").Range("A16") = 640
Sheets("Time&AV").Range("A17") = 660
Sheets("Datas").Select
Dim A, B, C, D, E, F, G, H, HH, J, K, L, M, N, O, P As Single
Dim ws As Worksheet
Set ws = Sheets("Datas")
A = Application.WorksheetFunction.Average(ws.Range("C2:C9963"))
Worksheets("Time&AV").Range("B2").Value = A
B = Application.WorksheetFunction.Average(ws.Range("G2:G9963"))
Worksheets("Time&AV").Range("B3").Value = B
C = Application.WorksheetFunction.Average(ws.Range("K2:K9963"))
Worksheets("Time&AV").Range("B4").Value = C
D = Application.WorksheetFunction.Average(ws.Range("O2:O9963"))
Worksheets("Time&AV").Range("B5").Value = D
E = Application.WorksheetFunction.Average(ws.Range("S2:S9963"))
Worksheets("Time&AV").Range("B6").Value = E
F = Application.WorksheetFunction.Average(ws.Range("W2:W9963"))
Worksheets("Time&AV").Range("B7").Value = F
G = Application.WorksheetFunction.Average(ws.Range("AA2:AA9963"))
Worksheets("Time&AV").Range("B8").Value = G
H = Application.WorksheetFunction.Average(ws.Range("AE2:AE9963"))
Worksheets("Time&AV").Range("B9").Value = H
HH = Application.WorksheetFunction.Average(ws.Range("AI2:AI9963"))
Worksheets("Time&AV").Range("B10").Value = HH
J = Application.WorksheetFunction.Average(ws.Range("AM2:AM9963"))
Worksheets("Time&AV").Range("B11").Value = J
K = Application.WorksheetFunction.Average(ws.Range("AQ2:AQ9963"))
Worksheets("Time&AV").Range("B12").Value = K
L = Application.WorksheetFunction.Average(ws.Range("AU2:AU9963"))
Worksheets("Time&AV").Range("B13").Value = L
M = Application.WorksheetFunction.Average(ws.Range("AY2:AY9963"))
Worksheets("Time&AV").Range("B14").Value = M
N = Application.WorksheetFunction.Average(ws.Range("BC2:BC9963"))
Worksheets("Time&AV").Range("B15").Value = N
O = Application.WorksheetFunction.Average(ws.Range("BG2:BG9963"))
Worksheets("Time&AV").Range("B16").Value = O
P = Application.WorksheetFunction.Average(ws.Range("BK2:BK9963"))
Worksheets("Time&AV").Range("B17").Value = P
Sheets("Time&AV").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time(s)"
ActiveChart.Axes(xlCategory).MinimumScale = 360
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "16/07 Average Velocity (m/s)"
End With
End Sub |
Partager