vba 如何将工作簿中的工作表复制到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11578569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy sheets from a workbook to another workbook
提问by Adrian
I have this code to copy a sheet from a Workbook in VBA/Access to another Workbook/File.
我有这个代码可以将工作簿中的工作表从 VBA/Access 复制到另一个工作簿/文件。
Dim File1 as String
Dim File2 as String
File1 = "D:\File1.xls"
File2 = "D:\File2.xls"
Windows(File1).Activate
Sheets("Name of Sheet").Select
Sheets("Name of Sheet").Copy Before:=Workbooks(File2).Sheets("Name of Target Sheet")
This is not working. I need to copy in background.Also to disable any macros.
这是行不通的。我需要在后台复制。还要禁用任何宏。
- How can I make it work?
- Can I give instead of "Sheet Name" an index?
- Can I give an array of indexes to copy to the second Workbook?
- 我怎样才能让它工作？
- 我可以给一个索引而不是“工作表名称”吗？
- 我可以提供要复制到第二个工作簿的索引数组吗？
回答by Fionnuala
If you are running in MS Access, you need something on these lines:
如果您在 MS Access 中运行，则需要在以下几行中进行操作：
Dim CopyFrom As Object
Dim CopyTo As Object ''Early binding: Workbook
Dim CopyThis As Object
Dim xl As Object ''Early binding: New Excel.Application
''Late binding
Set xl = CreateObject("Excel.Application")
xl.Visible = True
''To use a password: Workbooks.Open Filename:="Filename", Password:="Password"
Set CopyFrom = xl.Workbooks.Open("z:\docs\From.xls")
Set CopyThis = CopyFrom.Sheets(1) ''Sheet number 1
Set CopyTo = xl.Workbooks.Open("z:\docs\To.xls")
CopyThis.Copy After:=CopyTo.Sheets(CopyTo.Sheets.Count)
CopyFrom.Close False
回答by kirbs
You need to open the workbooks first.
您需要先打开工作簿。
Dim File1 As String
Dim File2 As String
File1 = "C:\Path\to\file\Book13.xlsx"
File2 = "C:\Path\to\file\Book2.xlsx"
Workbooks.Open Filename:=File2
Workbooks.Open Filename:=File1
ActiveWorkbook.Worksheets("Sheet2").Select
Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1)
You can use the sheet name or an index with the Sheets object. To copy multiple worksheets into another workbook you can pass an array into a loop.
您可以将工作表名称或索引与 Sheets 对象一起使用。要将多个工作表复制到另一个工作簿中，您可以将数组传递到循环中。
