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')

Public Sub 遍历()

Dim arr, brr

Application.ScreenUpdating = False

Application.DisplayAlerts = False

t = Timer

mypath = ThisWorkbook.Path &"\"

myname = Dir(mypath &"*.xls*")

ReDim brr(1 To 1000000, 1 To 6)

n = 1

Do While myname <>""

If myname <>ThisWorkbook.Name Then

Workbooks.Open (mypath &myname)

arr = ActiveSheet.Range("a1").CurrentRegion.Value

ActiveWorkbook.Close

For i = 1 To UBound(arr)

If arr(i, 5) = "签收" Or arr(i, 5) = "驿站代收" Or arr(i, 5) = "柜机代收" Then

For j = 1 To 6

brr(n, j) = arr(i, j)

Next

n = n + 1

End If

Next

End If

myname = Dir

Loop

ThisWorkbook.Sheets(1).[a1].Resize(1, 6) = arr

ThisWorkbook.Sheets(1).[a2].Resize(UBound(brr), 6) = brr

Columns(1).Select

Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 2), TrailingMinusNumbers:=True

MsgBox Timer - t

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

---