将Excel工作簿的一部分复制/粘贴到当前未打开的工作簿 [英] Copy/Paste part of Excel workbook to workbooks that are not currently open

问题描述

我需要将Excel工作簿的一部分复制到3500个其他工作簿中.由于工作簿的数量，在每个工作簿上运行宏是不可行的.

I need to copy a section of an Excel workbook to ~3500 other workbooks. Because of the number of workbooks, running a macro on each isn't feasible.

有没有一种方法可以将其复制并粘贴到其他工作簿中而不打开它们?

Is there a way to copy that piece and paste it to the other workbooks without having them open?

推荐答案

西红柿， 不幸的是，正如@Ken White和@ L42都指出的那样，要编辑文件，必须至少在某种程度上打开文件(即将其加载到内存中).更糟糕的是，除非您不愿意直接操作xml或二进制数据(不建议使用)，否则将不得不使用Excel为您打开.xlsx文件，这会花费一些时间.

Tomato, Unfortunately, as @Ken White and @L42 have both pointed out, in order to edit a file you must, at least on some level, open it (i.e. load it into memory). What's worse is that unless you're willing to manipulate the xml or binary data directly (not advised), you're going to have to use Excel to open the .xlsx file for you, which takes time.

当然，这并不意味着您必须手动进行.例如:

This does not, of course, mean that you have to do in manually. For instance:

Sub DataToOtherWorkbooks()
    Dim StrFile As String
    Dim SourceRange As Range
    Rng = "A1:A2"
    Set SourceRange = Range(Rng)

    StrFile = Dir("C:\ExcelWorkBooks\*.xlsx")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        Application.ScreenUpdating = False
        Set TargetWb = Workbooks.Open("C:\ExcelWorkBooks\" & StrFile)
        TargetWb.Worksheets("Sheet1").Range(Rng).Value(11) = SourceRange.Value(11)
        TargetWb.Save
        TargetWb.Close SaveChanges:=False
        StrFile = Dir
    Loop
End Sub

上面的代码将循环浏览扩展名为.xlsx的ExcelWorkBooks文件夹中的所有文件，并使用源工作簿指定范围内的数据填充它们.

The above code will loop through all files in the folder ExcelWorkBooks with a .xlsx extension and populate them with the data located in the designated range of your source workbook.

但是，如前所述，这将需要一些时间.当我在100个工作簿上(通过笔记本电脑)运行它时，每个工作簿平均花费0.54秒.因此，以您的情况而言，您要查看3500个工作簿，大约需要31分钟.速度不完全准确，但肯定比手动完成要好.希望这对您有用.请询问您是否还有其他问题.

As mentioned earlier, however, this will take some time. When I ran it on 100 workbooks (from my laptop) it took an average of 0.54 seconds per workbook. So, in your case, with 3,500 workbooks you're looking at about 31 minutes. Not exactly light speed, but certainly better than doing it by hand. Hope this is of use to you. Please ask if you have further questions.

补充

番茄，响应您的评论，要转移格式，包括您范围内单元格的合并或未合并性质，您将使用:

Tomato, in response to your comment, to transfer the format, including the merged or unmerged nature of the cells in your range you would use:

TargetWb.Worksheets("Sheet1").Range(Rng).Value(11) = SourceRange.Value(11)

我已经更新了原始代码以反映所做的更改.这应该为您解决，但请告诉我.

I've updated the original code to reflect the change. This should take care of it for you, but let me know.

这篇关于将Excel工作簿的一部分复制/粘贴到当前未打开的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！