vba 运行时错误 1004 无法将工作表从源复制到目标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12155471/
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
runtime error 1004 cant copy sheet from source to dest
提问by lorenz albert
Really this is curious. Let me try to explain what is going on. I wrote a macro in word that is calling a macro of an excelfile
这真的很好奇。让我试着解释一下发生了什么。我在 word 中写了一个宏，它调用了 excelfile 的宏
Set excel = CreateObject("excel.application")
excel.Visible = True
excel.workbooks.Open FileName:=tablePath
IDcolumn = excel.Application.Run("main", generatedExelPath)
the "main" function being called from the word macro is "simply" generating a new excel-file by copying specific data from the called excel file.
从宏这个词调用的“main”函数“简单地”通过从被调用的excel文件中复制特定数据来生成一个新的excel文件。
So we got 1 word and 2 excel documents. some sheets of excel1 have to be copied to excel2.
所以我们得到了 1 个单词和 2 个 excel 文档。必须将某些 excel1 表复制到 excel2。
therefore I use:
因此我使用：
For i = 0 To UBound(copySheets)
Windows(srcWinName).Activate
Sheets(copySheets(i)).Activate
Set AcSh = ActiveSheet
Windows(destWinName).Activate
If copySheets(i) = "config" Then
AcSh.Copy Before:=Sheets(1)
Else
AcSh.Copy After:=ActiveSheet
End If
Next i
where copySheets contains the sheets that needs to be copied. But the copy commands throws an error. Something like "can't copy from source to destination cause the destination has less rows and columns than the source file"
其中 copySheets 包含需要复制的工作表。但是复制命令会引发错误。诸如“无法从源文件复制到目标文件，因为目标文件的行数和列数少于源文件”之类的内容
Atm excel1 is an xlsmas it was an xlsfile this error didn't occur.
I wonder if this line, which adds the excel2 is the prob
Atm excel1 是一个没有发生此错误xlsm的xls文件。我想知道添加 excel2 的这一行是否是问题
Workbooks.Add Template:="Workbook"
maybe i should use another template but I don't know which one.
也许我应该使用另一个模板，但我不知道是哪个。
If i create an empty xlsmfile, save it and then use open this empty xlsminstead of
the command before the error doesn't occur. Hope someone can help me.
如果我创建一个空xlsm文件，请保存它，然后xlsm在错误不会发生之前使用 open this empty而不是命令。希望可以有人帮帮我。
Ahh and one thing I should mention is: if I run the excel macro as above from the word macro the error occurs but when I close Word and manually execute the excel macro it works.
啊，我应该提到的一件事是：如果我从 word 宏运行上述 excel 宏，则会发生错误，但是当我关闭 Word 并手动执行 excel 宏时，它会起作用。
Edit: It works
编辑：它有效
I just changed the excel-options as Alain said. here his post.
正如 Alain 所说，我只是更改了 excel 选项。这是他的帖子。
Oh I know why that's happening. You have to change your default excel format: Orb > Excel Options > |Save| > Save files in this format: "Excel Workbook (.xlsx)". Your current setting will be Excel 97-2003 Workbook (.xls). – Alain Aug 29 at 12:09
哦，我知道为什么会这样。您必须更改默认的 excel 格式：Orb > Excel 选项 > |保存| > 以这种格式保存文件：“Excel 工作簿 (.xlsx)”。您当前的设置将是 Excel 97-2003 工作簿 (.xls)。– 阿兰 8 月 29 日 12:09
采纳答案by Alain
The .xlsextension is the Excel 2003 workbook format. These support only 65,536 rows and 256 columns.
该.xls扩展是Excel 2003工作簿格式。这些仅支持 65,536 行和 256 列。
.xlsx,.xlsb,.xlsmare Excel 2007 workbooks. These support up to 1,048,576 rows and 16,384 columns.
.xlsx、.xlsb、.xlsm是 Excel 2007 工作簿。它们最多支持 1,048,576 行和 16,384 列。
This error is occurring because you are trying to copy a worksheet from a 2007 workbook to a 2003 workbook. This cannot be done because the of the row and column count limitation, even if you are not actually making use of all those extra rows and columns in the sheet being copied.
发生此错误是因为您正尝试将工作表从 2007 年工作簿复制到 2003 年工作簿。由于行数和列数的限制，即使您实际上并未使用正在复制的工作表中所有这些额外的行和列，也无法做到这一点。
The solution is to save the .xlsmworkbook as a .xlsfile or vice versa, and then copy the sheet over. Alternatively, rather than copying the entire sheet, you can copy just the UsedRange from one sheet to another, but risk losing important formatting such as row heights and column widths.
解决方案是将.xlsm工作簿另存为.xls文件，反之亦然，然后将工作表复制过来。或者，您可以只将 UsedRange 从一张工作表复制到另一张工作表，而不是复制整个工作表，但可能会丢失重要的格式，例如行高和列宽。
Adding a new workbook will create a workbook in your default save format. You can ensure this is progrmatically set to a .xlsx file by forcing the default save format in VBA and then changing it back so that you don't upset the user:
添加新工作簿将以您的默认保存格式创建一个工作簿。您可以通过在 VBA 中强制使用默认保存格式，然后将其改回，从而确保以编程方式将其设置为 .xlsx 文件，以免让用户感到不安：
Dim prevSaveFormat As Long
prevSaveFormat = Application.DefaultSaveFormat
Application.DefaultSaveFormat = 51 'xlsx
Workbooks.Add
Application.DefaultSaveFormat = prevSaveFormat
A full list of file format constants is available at XlFileFormat Enumerationbut here are the common ones:
XlFileFormat Enumeration 中提供了完整的文件格式常量列表，但这里是常见的：
51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)
51 = xlOpenXMLWorkbook（2007-2010 年没有宏，xlsx）
52 = xlOpenXMLWorkbookMacroEnabled（在 2007-2010 年有或没有宏，xlsm）
50 = xlExcel12（2007-2010 年的 Excel 二进制工作簿，带或不带宏，xlsb）
56 = xlExcel8（Excel 2007-2010 中的 97-2003 格式，xls）
