将来自多个工作簿的数据组合成主机 [英] Combining data from multiple workbooks into a master

问题描述

我一直在搜索论坛，只是无法解决我的代码问题。我非常新的宏，我确定这是一个简单的东西，像一些变量没有被定义，但我无法弄清楚。
我正在尝试将数据从多个工作簿加载到主服务器，真的需要帮助！

I have been searching forums and just can't work out the issue with my code. I am very new to macros and I'm sure it's something simple, like some variable not being defined, but I can't figure it out. I am trying to load data from multiple workbooks into a master and really need help please!

Dir for source files: C:\Test Dir\
Dir for Master: C:\Test Dir\Master\

源文件名不同，但都以* FORMATTED.xlsx结尾。

Source filenames differ, but all end in "*FORMATTED.xlsx."

主文件名：工资核算师。 xlsx

源工作表名称=加载数据

Source worksheet name = "Loaded Data"

主工作表名称=摘要

所有SOURCE数据位于A2：J106。

All SOURCE data is in A2:J106.

源和主文件是列标题，相同。
我正在将所有数据加载到主文件摘要工作表中。

The top row in the source and Master files are column headers and are identical. I am loading all data into the Master file "Summary" worksheet.

我最新的错误是：运行时错误 1004：选择Range类失败的方法。在Sheets（Loaded Data）。Range（A2：J106）。选择行

这是我目前的代码：

Sub combine_data()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
'Dim MyTemplate As Workbook
'Dim SumTemplate As Workbook
MyPath = "C:\Test Dir\"
SumPath = "C:\Test Dir\Master\"
MyTemplate = "*.xlsx"  'Set the template.
SumTemplate = "Payroll MASTER.xlsx"
MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
SumName = Dir(SumPath & SumTemplate)

Do While MyName <> ""
    Workbooks.Open MyPath & MyName
Sheets("Loaded Data").Range("A2:J106").Select
Selection.Copy
    Workbooks.Open SumPath & SumName
Sheets("Summary").Select
Range("A65536").End(xlUp).Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(MyName).Close SaveChanges:=False        'close
Workbooks(SumName).Close SaveChanges:=True
MyName = Dir                    'Get next file
Loop
End Sub

谢谢！

推荐答案

为了减少错误，您应该在模块顶部声明 Option Explicit 。然后，您将会被告知使用未声明的变量，并减少拼写变量名称的风险。

To reduce bugs, you should state Option Explicit at the top of the module. You will then be told when using variables that are not declared and you reduce the risk of misspelling the names of variables.

您应该将 SumName =循环前的Dir（SumPath& SumTemplate），作为 Do While结尾的 Dir ..循环将引用具有参数的LAST 目录。当您使用选择您所描述的错误过去时，您遇到了这个问题。

You should put the SumName = Dir(SumPath & SumTemplate) just before the loop, as the Dir at the end of your Do While ... Loop will refer to the LAST Dir that had parameters. When getting past the error with the Select that you describe, you have ran into this problem.

在您的循环中，您应该单独参考每个工作簿/工作表，以澄清你在做什么（帮助自己的未来）。

Inside your loop, you should refer to each workbook/worksheet individually, to clarify what you are doing (helping yourself for the future).

您正在为每个源文件打开并关闭MASTER文件。你可以在循环之前打开它，然后关闭它。这将使您的脚本更快。

You are opening and closing the MASTER file for every source-file. You could open it before the Loop and close it after. This will make your script faster.

以下是使用上述注释修改的代码：

Here is the code modified with the above comments:

Option Explicit

Sub combine_data()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As String
Dim SumTemplate As String
Dim myWS As Worksheet
Dim sumWS As Worksheet

'Define folders and filenames
MyPath = "C:\Test Dir\"
SumPath = "C:\Test Dir\Master\"
MyTemplate = "*.xlsx"  'Set the template.
SumTemplate = "Payroll MASTER.xlsx"

'Open the template file and get the Worksheet to put the data into
SumName = Dir(SumPath & SumTemplate)
Workbooks.Open SumPath & SumName
Set sumWS = ActiveWorkbook.Worksheets("Summary")

'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
Do While MyName <> ""
    'Open the source file and get the worksheet with the data we want.
    Workbooks.Open MyPath & MyName
    Set myWS = ActiveWorkbook.Worksheets("Loaded Data")
    'Copy the data from the source and paste at the end of Summary sheet
    myWS.Range("A2:J106").Copy
    sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    'Close the current sourcefile and get the next
    Workbooks(MyName).Close SaveChanges:=False        'close
    MyName = Dir                    'Get next file
Loop
'Now all sourcefiles are copied into the Template file. Close and save it
Workbooks(SumName).Close SaveChanges:=True
End Sub

这篇关于将来自多个工作簿的数据组合成主机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！