VBA中提取,一般要先打开工作簿,对Workbooks(指定的文件名).WorkSheets(包含数据的需要的Sheet名称).Range(数据所在的区域)
逐级进行约束,得到数据区域,进行取值。
示例:
Private Sub CommandButton1_Click()
Dim Arr, i&, Nm, col%
Dim strNm As String, sPth As String, sFl As String
Dim WB As Workbook
strNm = "断断" '先组成 “1,2,3,4,……”这样的字符串
For i = 1 To 40
strNm = strNm & "," & i '也可以用其他分隔符
Next i
Nm = Split(strNm, ",")
sPth = "D:\" '改为实际的路径——也可以从某一个单元格读取
For Each sFl In Array("Book1.xlsx", "Book2.xlsx") '文件名改为实际两个文件名
Set WB = Application.Workbooks.Open(sPth & sFl, UpdateLinks:=True)
Application.ScreenUpdating = False '关闭屏幕刷新,能加快处理速度
Sheets("提取").Activate: Row = 1
Cells.ClearContents
For i = 0 To UBound(Nm)
With WB.Sheets(Nm(i))
For j = 19 To 28
If Not Application.WorksheetFunction.IsError(.Cells(2, j)) Then
If .Cells(2, j) = "有" Then
Cells(Row, 36) = .Cells(6, j): Cells(Row, 37) = .Cells(8, j)
.Cells(2, j).Resize(3, 1).Copy
Cells(Row, 38).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'Cells(1, col).Resize(7, 1) = .Cells(2, j).Resize(7, 1).Value
Row = Row + 1
End If
End If
Next
End With
Next
WB.Close SaveChanges:=False '不保存更改。也可设置为True——保存更改
Next
Application.ScreenUpdating = True
End Sub