Excel VbA 读取某个文件夹下所有文件的内容

Excel VbA 读取某个文件夹下所有文件的内容

Sub GetAFolderMoreWorkBooks() Dim fPath As String Dim fName As String Dim sName As String

Dim fNameCount As Integer Dim fNameList() As String

Dim rValue As Variant

Dim j As Integer Dim oldCol As Integer j = 1 oldCol = 11 newCol = 1

‘文件数目 ‘文件名列表

‘路径名 ‘文件名 ‘工作表名称 ‘单元格范围

Dim cellRange As String

‘某个单元格中的值

‘文件夹下表格的活动单元格 ‘新生成表格的活动单元格

Dim newCol As Integer

fPath = \デスクトップ￥check\ sName = \レビュー記録\

fName = Dir(fPath & \

While fName <> \

fNameCount = fNameCount + 1

ReDim Preserve fNameList(1 To fNameCount) fNameList(fNameCount) = fName fName = Dir Wend

Workbooks.Add

If fNameCount = 0 Then Exit Sub

rValue = 1

For j = 1 To fNameCount

‘获取该文件夹下所有表格的名字

oldCol = 11 rValue = 1

While rValue <> 0

oldRange = \ newRange = \

Cells(newCol, 1).Formula = fNameList(j) 'MsgBox \

If rValue <> 0 Then

With ActiveSheet.Range(newRange)

.FormulaArray = \oldRange

.Value = .Value End With End If

oldCol = oldCol + 1 newCol = newCol + 1

rValue = ExecuteExcel4Macro(\& \ Wend Next j