我分了两个模块，当然也可以放在一起，话不多说上代码！

“模块二”中执行方法，调用模块一中的方法

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

 

部分语句参考自：自动对一个文件夹下的N个word文件批量执行一个宏_nxhujiee_新浪博客