|
下記のコードに変更してください。
'結果のファイルを開く
Private Sub ExGetTotoData(sdir As String, lcount As Long)
If Right(sdir, 1) <> "\" Then sdir = sdir & "\"
Set tExcel = CreateObject("Excel.Application")
tExcel.Visible = True
Call ExGetResultData(sdir, lcount)
tExcel.Visible = False
Set tExcel = Nothing
End Sub
下記のコードを追加してください。
Function ExCountYoso(tObj As Object) As Long
Dim i As Integer
Dim lyoso1 As Long
Dim lyoso2 As Long
Dim lyoso3 As Long
Dim lres As Long
Dim lret As Long
lret = 0
For i = 1 To 13
lres = tObj.Worksheets(1).Cells(i + 4, 7).Value
lyoso1 = tObj.Worksheets(1).Cells(i * 3 + 4, 12).Value
lyoso2 = tObj.Worksheets(1).Cells(i * 3 + 4, 13).Value
lyoso3 = tObj.Worksheets(1).Cells(i * 3 + 4, 14).Value
If lres = 1 Then
If lyoso1 > lyoso2 And lyoso1 > lyoso3 Then
lret = lret + 1
End If
ElseIf lres = 0 Then
If lyoso2 > lyoso1 And lyoso2 > lyoso3 Then
lret = lret + 1
End If
ElseIf lres = 2 Then
If lyoso3 > lyoso1 And lyoso3 > lyoso2 Then
lret = lret + 1
End If
End If
Next
ExCountYoso = lret
End Function
Private Sub ExGetResultData(sdir As String, lcount As Long)
Dim i As Integer
Dim tObj As Object
Dim ltotaldraw As Long
Dim ltotalhome As Long
Dim lresdraw(13) As Long
Dim lreshome(13) As Long
Dim ldraw As Long
Dim lhome As Long
Dim sfina As String
Dim lYoso As Long
ltotaldraw = 0
ltotalhome = 0
For i = 0 To 13
lresdraw(i) = 0
lreshome(i) = 0
Next
For i = 1 To lcount
sfina = Cells(10 + i - 1, 2)
Set tObj = tExcel.Application.Workbooks.Open(Filename:=sdir & sfina)
Call ExCountDraw(tObj, ldraw, lhome)
Cells(10 + i - 1, 3) = ldraw
Cells(10 + i - 1, 4) = lhome
ltotaldraw = ltotaldraw + ldraw
ltotalhome = ltotalhome + lhome
lresdraw(ldraw) = lresdraw(ldraw) + 1
lreshome(lhome) = lreshome(lhome) + 1
lYoso = ExCountYoso(tObj)
Cells(10 + i - 1, 5) = lYoso
'保存せずに閉じる
tObj.Close SaveChanges:=False
Set tObj = Nothing
Next
Range("I3") = lcount * 13
Range("K3") = ltotaldraw
Range("K4") = Format(ltotaldraw / lcount, "0.0")
Range("K5") = Format((ltotaldraw / (lcount * 13)) * 100, "0.0")
Range("L3") = ltotalhome
Range("L4") = Format(ltotalhome / lcount, "0.0")
Range("L5") = Format((ltotalhome / (lcount * 13)) * 100, "0.0")
ExGetMax 11, lresdraw
ExGetMax 12, lreshome
End Sub
|
|
|
|
|
|
|
|
|
|
