批量统计文件夹内xlsx行数

最新推荐文章于 2024-07-22 02:45:18 发布

专注BI

最新推荐文章于 2024-07-22 02:45:18 发布

阅读量1.9k

点赞数 2

分类专栏：批处理文件

本文链接：https://blog.csdn.net/weixin_41668007/article/details/118691900

版权

批处理文件专栏收录该内容

3 篇文章 2 订阅

订阅专栏

文件夹里有多个excel表格，得到每个表格的行数，保存为vbs格式

Set fs = CreateObject("scripting.FileSystemObject")
Set excel = CreateObject("excel.application")
Set resultBookSheet = excel.Workbooks.Add().Sheets(1)
excel.visible = true
resultBookSheet.Visible = True
resultBookSheet.activate
i = 1
For Each fileName In fs.GetFolder(CreateObject("WScript.Shell").CurrentDirectory).Files
                                                                                                                                                          
    If Right(fileName, 5) = ".xlsx" Then
                                                                                                                                                          
        Set book = excel.Workbooks.Open(fileName)
                                                                                                                                                              
        resultBookSheet.Cells(i, 1) = fs.getfileName(fileName)
                                                                                                                                                              
        For Each sheet In book.Sheets
            resultBookSheet.Cells(i, 2) = sheet.Name
                                                                                                                                                                  
            resultBookSheet.Cells(i, 3) = sheet.UsedRange.Rows.Count + sheet.UsedRange.Row - 1
                                                                                                                                                                  
            i = i + 1
        Next
                                                                                                                                                              
        book.Close False
                                                                                                                                                              
        Set book = Nothing
    End If
Next

如果是xls则修改成这样即可