何时迭代目录中所有文件的总和?

编程入门行业动态更新时间:2024-10-22 23:00:19

本文介绍了何时迭代目录中所有文件的总和?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

目录调查中的所有文件都具有相同的结构. 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