一、简化版
我分了两个模块，当然也可以放在一起，话不多说上代码！
“模块二”中执行方法，调用模块一中的方法 Sub 批量操作excel月报() ' ' 批量操作excel月报 宏 ' ' 快捷键: Ctrl+p ' Dim path As String Dim FileName As String Dim exceldoc As excel.Workbook Dim MyDir As String Application.ScreenUpdating = False MyDir = "C:\Users\yuhd\Desktop\新建文件夹\新建文件夹" '文件夹路径根据需要自己修改，需要处理的文件都放该文件夹内 'MyDir = ThisWorkbook.Path & "\新建文件夹" FileName = Dir(MyDir & "\*.xls*") Do While FileName <> "" 'Do Until FileName = "" If FileName <> ThisWorkbook.Name Then Set exceldoc = Workbooks.Open(MyDir & "\" & FileName) exceldoc.Activate Call 模块1.一键生成sql语句 '调用宏,换成你自己宏的名字 Application.CutCopyMode = False exceldoc.Close True FileName = Dir() End If Loop Set exceldoc = Nothing Application.ScreenUpdating = True End Sub 被调用的“模块一”中的方法 Sub 一键生成sql语句() ' ' 一键生成sql语句 宏 ' ' 快捷键: Ctrl+r ' '1. 先执行各个sheet的分别汇总统计 Dim r1 As String Dim r1y As String r1 = "E2" r1y = "B" Sheets("elk地域原数据").Select Range(Range(r1), Cells(Range(r1y & Rows.Count).End(xlUp).Row, Range(r1).Column)).Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '""&RC[-3]&""', '', '', ""&RC[-2]&"", 1),""" Sheets("每月活跃用户数").Select Range("G1").Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '', '', '', ""&INDIRECT(""B""&COUNTA(C[-5]))&"", 1),""" Sheets("汇总数据").Select Range("N1").Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '', '""&RC[-12]&""', '', ""&INDIRECT(""B""&COUNTA(C[-12]))&"", 1),""" Range("N2").Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '', '""&R[-1]C[-11]&""', '', ""&INDIRECT(""C""&COUNTA(C[-11]))&"", 1),""" Range("N3").Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '', '""&R[-2]C[-10]&""', '', ""&INDIRECT(""D""&COUNTA(C[-10]))&"", 1),""" Dim r2 As String Dim r2y As String r2 = "D12" r2y = "A" Sheets("在线品牌分布").Select Range(Range(r2), Cells(Range(r2y & Rows.Count).End(xlUp).Row, Range(r2).Column)).Select Selection.Formula2R1C1 = _ "= ""('""&TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")&""', '""&TEXT(TEXT(EOMONTH(MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,FIND(""]"",CELL(""filename""))-FIND(""["",CELL(""filename""))-11),0),""yyyy-mm-dd"")+1,""yyyy-mm-dd"")&"" 00:00:01', 'uc'," & _ " '', '', '""&RC[-3]&""', ""&RC[-2]&"", 1),""" ' '2. 再把所有sheet生成的语句汇总到一个sheet中 ' Sheets.Add Before:=Sheets(1) Sheets(1).Name = "VB生成SQL语句" '月活总数 Sheets("每月活跃用户数").Select Range("G1").Copy Sheets(1).Select Range("A1").Select Selection.Value = "-- 月活总数" Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues '省份 Sheets("elk地域原数据").Select Range(Range(r1), Cells(Range(r1y & Rows.Count).End(xlUp).Row, Range(r1).Column)).Copy Sheets(1).Select Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.Value = "-- 省份" Selection.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues '版本 Sheets("汇总数据").Select Range("N1:N3").Copy Sheets(1).Select Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.Value = "-- 版本" Selection.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues '厂商 Sheets("在线品牌分布").Select Range(Range(r2), Cells(Range(r2y & Rows.Count).End(xlUp).Row, Range(r2).Column)).Copy Sheets(1).Select Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.Value = "-- 厂商" Selection.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub 二、简化版 上面是简化版的，接下来实际项目中用到的， “模块二”中执行方法，调用模块
