excelvba遍历文件夹里的所有表格添加同一页

import pandas as pd

import os

# 用os.walk遍历文件；用.endswith判断文件后缀

dfs = pd.DataFrame()

for root, dirs, files in os.walk(r'C:\Users\ll\凳高Desktop\pandas\excels'):

for file in files:

if file.endswith('.xlsx'):

# 构建绝对路径

file_name = os.path.join(root, file)

# print(file_name)

df = pd.read_excel(file_name)

# print(df)

dfs = pd.concat([dfs, df])

# print(dfs)

dfs.to_excel(r'C:\Users\清如ll\Desktop\答粗启new.xlsx')

首先要打开你需要的数个文件，森誉而每个文件清念假设在VBA所在工作簿的 A列，从A1开始答春困，并且文件名排列下去，中间没有空白单元格，直到空白的单元格结束。需要求和的是每个文件内的shee1的A2单元格，最后合计数在程序所在文件的b1单元格

public sub 遍历()

dim i as integer

dim sumnumber as long

dim filename as string

i = 1

do until cells(i,1）=“”

cells(i,1) = filename

workboos.open filename: = filename

Windows(filename).Activate

sumnumber = sumnumber + sheets("sheet1").range("a2")

activewindows.close

i = i+1

loop

sumnumber = range("b1")

end sub

Excel文件格式一致，汇总求和，其他需求自行变通容

汇悄吵总使用了字典启培侍

Public d

Sub 按钮1_Click()

Application.ScreenUpdating = False

ActiveSheet.UsedRange.ClearContents

Cells(1, 1) = "编号"

Cells(1, 2) = "数量"

Set d = CreateObject("scripting.dictionary")

Getfd (ThisWorkbook.Path) 'ThisWorkbook.Path是当前代码文件所在路径，路径名可以根据需求修改

Application.ScreenUpdating = True

If d.Count >0 Then

ThisWorkbook.Sheets(1).[a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)

ThisWorkbook.Sheets(1).[b2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)

End If

End Sub

Sub Getfd(ByVal pth)

Set Fso = CreateObject("scripting.filesystemobject")

Set ff = Fso.getfolder(pth)

For Each f In ff.Files

Rem 具体提取哪类文件，还是需要根据文件扩展名进行处理

If InStr(Split(f.Name, ".")(UBound(Split(f.Name, "."))), "xl") >0 Then

If f.Name <中高>ThisWorkbook.Name Then

Set wb = Workbooks.Open(f)

For Each sht In wb.Sheets

If WorksheetFunction.CountA(sht.UsedRange) >1 Then

arr = sht.UsedRange

For j = 2 To UBound(arr)

d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)

Next j

End If

Next sht

wb.Close False

End If

End If

Next f

For Each fd In ff.subfolders

Getfd (fd)

Next fd

End Sub

---