VBA EXCEL 工作簿之间相互操作

说明：

Function CheckAndAddStyle()

Dim sheetToWorkBookName As String

Dim nameIndexInBJ As Integer

For Each sheet In ThisWorkbook.Sheets

sheetToWorkBookName = CStr(2014) + Left(sheet.Name, 2) + ".xls"

Dim myApp As New Application

Dim sh As Worksheet

Dim Temp As String

Temp = ThisWorkbook.Path & "" & sheetToWorkBookName

myApp.Visible = False

Set sh = myApp.Workbooks.Open(Temp).Sheets(1)

nameIndexInBJ = GetNameIndex(sh)

For bjRow = 2 To sh.UsedRange.Rows.Count

Dim yeRow As Integer

For yeRow = 5 To sheet.UsedRange.Rows.Count

If sh.Cells(bjRow, nameIndexInBJ).Value = sheet.Cells(yeRow, 1) Then

sh.Cells(bjRow, nameIndexInBJ).Interior.ColorIndex = 42

Exit For

End If

Next

If yeRow = sheet.UsedRange.Rows.Count + 1 Then

sh.Cells(bjRow, nameIndexInBJ).Interior.ColorIndex = 46

End If

Next

myApp.Quit

Set sh = Nothing

Set myApp = Nothing

Next

End Function

Function GetNameIndex(sheet As Worksheet) As Integer

Dim nameIndex As Integer

For i = 1 To sheet.UsedRange.Columns.Count

If sheet.Cells(1, i).Value = "姓名" Then

nameIndex = i

Exit For

End If

Next

GetNameIndex = nameIndex

End Function