Sub 导入数据()
Dim wb As Workbook, sht As Worksheet
Dim fn, fp As String
Dim i_row, i_col As Integer
Dim i As Integer
Dim school As String
school = ThisWorkbook.Sheets(1).Cells(1, 'B')
'清空数据
'ThisWorkbook.Sheets(1).Range('A3:G1048576').ClearContents
'找到空行行号
i_row = Range('E1048576').End(xlUp).Row 1
'i_row = 3
'Application.ScreenUpdating = False
'当前路径
If str_filepath = '' Then str_filepath = ThisWorkbook.Path
fn = Dir(str_filepath & '\*.xlsx')
Do While fn <> ''
'本文件除外
If fn <> ThisWorkbook.Name Then
'合成路径
fp = str_filepath & '\' & fn
'取得工作簿workbook
Set wb = GetObject(fp)
'对每一个工作表
For Each sht In wb.Worksheets
'找到指定学校所在行
i = WorksheetFunction.Match(school, sht.Range('A1:A1000'), 0)
'拷贝两行数据
sht.Range(sht.Cells(i, 'A'), sht.Cells(i 1, 'D')).Copy
ThisWorkbook.Sheets(1).Cells(i_row, 'A').PasteSpecial Paste:=xlPasteValues
'保存路径和工作表名称
ThisWorkbook.Sheets(1).Cells(i_row, 'E') = str_filepath
ThisWorkbook.Sheets(1).Cells(i_row, 'F') = wb.Name
ThisWorkbook.Sheets(1).Cells(i_row, 'G') = sht.Name
ThisWorkbook.Sheets(1).Cells(i_row 1, 'E') = str_filepath
ThisWorkbook.Sheets(1).Cells(i_row 1, 'F') = wb.Name
ThisWorkbook.Sheets(1).Cells(i_row 1, 'G') = sht.Name
i_row = i_row 2
Next
wb.Close False
End If
fn = Dir
Loop
'Application.ScreenUpdating = True
End Sub