|
Sub 导入数据()
Dim MyFile As Object
Dim Arr, ArrName()
Dim Rc%, K%
Dim FileName$, Str$
Dim Wb As Workbook
Set MyFile = CreateObject("scripting.filesystemobject")
FileName = Dir(ThisWorkbook.Path & "\*.xlsx", 16)
Do While FileName <> ""
K = K + 1
ReDim Preserve ArrName(1 To K)
ArrName(K) = ThisWorkbook.Path & "\" & FileName
FileName = Dir
Loop
With ThisWorkbook.Sheets("Sheet1")
.Range("A1").CurrentRegion = ""
.Range("A1:C1") = Array("类别", "值", "姓名")
End With
For K = 1 To UBound(ArrName)
Str = MyFile.getbasename(ArrName(K))
Set Wb = Workbooks.Open(ArrName(K))
With Wb
.Sheets(1).Range("C1") = "姓名"
Rc = .Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
.Sheets(1).Range("C2:C" & Rc) = VBA.Right(Str, VBA.Len(Str) - 2)
Arr = .Sheets(1).Range("A1").CurrentRegion.Offset(1)
.Close False
End With
With ThisWorkbook.Sheets("Sheet1")
.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(Arr), 3) = Arr
End With
Next K
Set MyFile = Nothing
Set Wb = Nothing
End Sub |
|