Excel-对象工作表的Mehod粘贴失败 [英] Excel - Mehod Paste of object worksheet failed

问题描述

在MS Access 2003 vba中处理某些代码.自动创建Excel文件.上周工作了.别以为我改变了什么.但是现在我在这个问题上弄错了.之所以要使用TransferSpreadsheet，然后再进行复制/粘贴，是因为它比逐个单元地复制到新工作表中要快得多.我通常为此使用模板(xlt)文件，但是这次决定从头开始创建最终工作簿，以避免需要xlt文件.

Working on some code in MS Access 2003 vba. Automating creation of Excel file. This worked last week. Don''t think I changed anything. But now I''m getting the error in the subject. The reason for the TransferSpreadsheet and then the copy/paste is that it''s much faster than copying cell by cell into a new sheet. I usually use a template (xlt) file for this but this time decided to create the end workbook from scratch to avoid the need for an xlt file.

Here''s my code. The error occurs on the last line shown below (I saw some suggestions regarding this error to use PasteSpecial but that didn''t help).

''*****************************************************************************************
''do fast dirty copy to a grungy spreadsheet
''*****************************************************************************************

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo." & sExportTempTableName, Replace(strSaveToPathAndFileName, ".xls", "_TEMP.xls"), True

''*****************************************************************************************
''Open up the grungy one so we can transfer the data over to the nice one
''*****************************************************************************************

Dim xlGrungy As New Excel.Application
Dim xlWkBkGrungy As New Excel.Workbook
Dim xlWkShtGrungy As New Excel.Worksheet
Set xlGrungy = CreateObject("Excel.Application")

xlGrungy.Workbooks.Open Replace(strSaveToPathAndFileName, ".xls", "_TEMP.xls")
Set xlWkBkGrungy = xlGrungy.ActiveWorkbook
Set xlWkShtGrungy = xlWkBkGrungy.Worksheets(1)

xlWkShtGrungy.Range("A2:AB" & Trim(str(NumFinalRows + 10))).Select ''skip the header row
xlGrungy.Selection.Copy

''*****************************************************************************************
''create fancy spreadsheet & Paste the cells from the grungy one into the nice one and format it
''*****************************************************************************************

Dim xlFinal As Excel.Application
Set xlFinal = CreateObject("Excel.Application")
Dim xlWkBkFinal As Excel.Workbook
Set xlWkBkFinal = xlFinal.Workbooks.Add

Dim xlWkShtFinal As Excel.Worksheet
Set xlWkShtFinal = xlWkBkFinal.Worksheets(1)

With xlWkShtFinal

    .Range("A2:A2").Select
    .Paste

推荐答案

如果从头开始创建最终工作簿，则需要自己添加工作表.当使用excel作为桌面应用程序手动创建新工作簿时，默认情况下会创建3张工作表.这些不是在通过代码执行时创建的.

尝试更改此内容:

If you create the end workbook from scratch you need to add the worksheets yourself. When creating a new workbook by hand using excel as desktop application there are 3 sheets created by default. Those aren''t created when you do that from code.

Try change this:

Set xlWkShtFinal = xlWkBkFinal.Worksheets(1)

对此:

to this:

Set xlWkShtFinal = xlWkBkFinal.Worksheets.Add

祝你好运！

Good luck!

这篇关于Excel-对象工作表的Mehod粘贴失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！