何时迭代目录中所有文件的总和? [英] How to calculate the sum for all files in a directory when to iterate them?

问题描述

目录调查中的所有文件都具有相同的结构.
x1.xls 中的数据:

x2.xls 中的数据:

我想同时获取 x1.xls 和 x2.xsl 的 b 列和 c 列的总和.br> x1.xls 的结果.

All files in directory survey contain same structure.
Data in x1.xls:

Data in x2.xls:

I want to get sum of b column and c column for both x1.xls and x2.xsl.
Result for x1.xls.

与 x2.xls 相同.
我可以执行以下步骤:
1.在 x1.xls
中打开vb编辑器2.编辑下面的 sub 并将 ctrl + z 与 sub sum 绑定.

Same sum for x2.xls.
I can do with following steps:
1.Open vb editor in x1.xls
2.Edit the below sub and bind ctrl+z with the sub sum.

Sub Sum()
    Dim BottomOfTable As Long
    BottomOfTable = Cells(Rows.Count, "A").End(xlUp).Row
    Cells(BottomOfTable + 1, "A").Value = "score"
    Range("B" & BottomOfTable + 1).Select
    Selection.FormulaR1C1 = "=round(SUM(R[-" & BottomOfTable - 1 & "]C:R[-1]C)" & ",2)"
    Selection.AutoFill Destination:=Range("b" & BottomOfTable + 1 & ":" & "c" & BottomOfTable + 1), Type:=xlFillDefault
    Range("b" & BottomOfTable + 1 & ":" & "c" & BottomOfTable + 1).Select    
End Sub

不同的文件包含不同的行，因此请使用 Cells(Rows.Count，"A").End(xlUp).Row 获取不同文件的动态行.

Different files contain different rows,so use Cells(Rows.Count, "A").End(xlUp).Row to get dynamic rows for different files.

3.按 x1.xls 中的 ctrl + z .
4.打开 x2.xls ，然后按 ctrl + z .

3.Press ctrl+z in x1.xls.
4.Open x2.xls and press ctrl+z.

现在我想使用vba自动化该过程.

Now i want to automate the process with vba.

这是我的尝试:

Sub ListDir()
Dim FileName As String
Dim myPath as string
myPath="d:\survey\"
FileName = Dir(myPath, vbNormal)
Do While FileName <> ""
    targetFile =  myPath & FileName
    sumColumns(targetFile)
    FileName = Dir()
Loop
End Sub


Function sumColumns(targetFile)    
    Dim BottomOfTable As Long, AK As Workbook
    Set AK = Workbooks.Open(targetFile)
    BottomOfTable = AK.Cells(Rows.Count, "A").End(xlUp).Row
    Cells(BottomOfTable + 1, "A").Value = "score"
    Range("B" & BottomOfTable + 1).Select
    Selection.FormulaR1C1 = "=round(SUM(R[-" & BottomOfTable - 1 & "]C:R[-1]C)" & ",2)"
    Selection.AutoFill Destination:=Range("b" & BottomOfTable + 1 & ":" & "c" & BottomOfTable + 1), Type:=xlFillDefault
    Range("b" & BottomOfTable + 1 & ":" & "c" & BottomOfTable + 1).Select  
    AK.Close  
End Function

当我在vba编辑器中执行 sub ListDir() x1.xsl 时，发生错误:

When i execute the sub ListDir() x1.xsl in vba editor ,an error occur:

sumColumns 函数中可能还有其他错误，如何解决以获取我期望的 survey 目录中所有文件的结果总和?

and maybe there are some other bugs in sumColumns function,how to fix to get what i expect result sum for all files in directory survey?

推荐答案

此代码应该与您要修改的文件位于不同的文件中.

This code should be in a different file than the ones you're trying to modify.

您可以使用 F8 键运行它，以便您说出您的代码和我的代码之间的变化.

You may run it using F8 key so you say what changes between your code and mine.

阅读评论并进行调整以适合您的需求.

Read the comments and adjust it to fit your needs.

' This option should go at the top of all modules when you write VBA code. So it warns you when you're not defining your variables
Option Explicit

Public Sub ListDir()
    Dim fileName As String
    Dim myPath As String
    Dim targetFilePath As String

    myPath = "d:\survey\"
    fileName = Dir(myPath, vbNormal)

    ' If something goes wrong, don't let the screen updating option off
    On Error GoTo CleanError

    ' Turn off screen updating so there is no flickering when opening and closing files
    Application.ScreenUpdating = False

    Do While fileName <> ""
        targetFilePath = myPath & fileName
        sumColumns targetFilePath
        fileName = Dir()
    Loop

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

CleanError:
    MsgBox "Something went wrong..."
    GoTo CleanExit
End Sub

Private Function sumColumns(targetFilePath)
    Dim targetWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim lastRow As Long

    ' Open and set a reference to workbook
    Set targetWorkbook = Workbooks.Open(targetFilePath)

    ' Set a reference to the first = 1 worksheet in file (beware that this may not always be the first visible sheet)
    Set targetSheet = targetWorkbook.Sheets(1)

    ' Get the row number of the last non empty cell in column A (here you should always be careful for what you're defining as non empty)
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Set the label in cell
    targetSheet.Range("A" & lastRow + 1).Value = "score"

    ' Set the formula to sum the values in each column
    targetSheet.Range("B" & lastRow + 1).FormulaR1C1 = "=round(SUM(R[-" & lastRow - 1 & "]C:R[-1]C)" & ",2)"
    targetSheet.Range("C" & lastRow + 1).FormulaR1C1 = "=round(SUM(R[-" & lastRow - 1 & "]C:R[-1]C)" & ",2)"

    ' Close saving changes
    targetWorkbook.Close True
End Function

如果有帮助，请记住标记答案.

Please remember to mark the answer if it helps.

这篇关于何时迭代目录中所有文件的总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！