专业崩溃时粘贴excel数据 [英] pastespecial Crashing when pasting excel data

问题描述

我正在使用以下代码从几个excel文件中获取数据并将其粘贴到一个新文件中：

  i = 0 To amountOfFiles  -  1 
 
 Dim xlAppSource As New Excel.Application 
 Dim xlAppTarget As New Excel.Application 
 Dim xlWbSource As Excel.Workbook 
 Dim xlWbTarget As Excel.Workbook 
 Dim xlsheetSource As Excel.Worksheet 
 Dim xlsheetTarget As Excel.Worksheet 
 
 Dim CurrentFile As String = strFileNames（i）
 Dim IntAmountOfRows As Integer = amountOfRows （CurrentFile）
 Dim intStartOfEmptyRow As Int16 = amountOfRows（SummaryLocation）
 
'设置当前工作簿
 xlWbSource = xlAppSource.Workbooks.Open（CurrentFile）
 xlWbTarget = xlAppTarget。 Workbooks.Open（SummaryLocation）
 
'设置当前工作表
 xlsheetSource = xlWbSource.ActiveSheet 
 xlsheetTarget = xlWbTar get.ActiveSheet 
 
'从源文件到目标文件的复制数据范围
 xlsheetSource.Range（A2：k& IntAmountOfRows）.Copy（）
 xlsheetTarget.Range（A2：k& intStartOfEmptyRow）.PasteSpecial（Excel.XlPasteType.xlPasteAll，Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone，False，False）
 
 '设置重点放在摘要ExcelSheet 
 xlWbTarget = xlAppSource.Workbooks.Open（SummaryLocation）
 xlsheetSource = xlWbTarget.ActiveSheet 
 
'close excel 
 xlWbSource.Close（True） 
 xlWbTarget.Close（True）
 xlAppSource.Quit（）
 xlAppTarget.Quit（）
 
'清理
 xlAppSource =没有
 xlAppTarget =没有
 xlWbSource =没有
 xlWbTarget =没有
 xlsheetSource =没有
 xlsheetTarget =没有
 
下一个
 但是，当我执行代码时，会抛出以下错误：
 
 
 
  
 
 
 
 方法PasteSpecial of Class Range has failed 
 
 
 
它指向的行： / p> 
 
 
  xlsheetTarget.Range（A2：k& intStartOfEmptyRow）.PasteSpecial（Excel.XlPasteType.xlPasteAll，Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone，False，False）
  
我不知道如何解决这个问题，看到它是一个非常普遍的错误，谷歌搜索给了我没有答案。
解决方案
我找到了一种方式来做我想要的，但是我不知道为什么。所以我不会接受我自己的答案，但等待有人解释。
 
 
 
我现在使用的代码是：
 
 $ b $从源到目标文件复制数据范围
 xlsheetSource.Range（A2：k& IntAmountOfRows）.Copy（）
 xlsheetTarget。 Range（A& intStartOfEmptyRow）.PasteSpecial（Excel.XlPasteType.xlPasteValues）
  

I am using the following code to get data from several excel files and paste them into a single new file:

    For i = 0 To amountOfFiles - 1

        Dim xlAppSource As New Excel.Application
        Dim xlAppTarget As New Excel.Application
        Dim xlWbSource As Excel.Workbook
        Dim xlWbTarget As Excel.Workbook
        Dim xlsheetSource As Excel.Worksheet
        Dim xlsheetTarget As Excel.Worksheet

        Dim CurrentFile As String = strFileNames(i)
        Dim IntAmountOfRows As Integer = amountOfRows(CurrentFile)
        Dim intStartOfEmptyRow As Int16 = amountOfRows(SummaryLocation)

        'Set current workbook
        xlWbSource = xlAppSource.Workbooks.Open(CurrentFile)
        xlWbTarget = xlAppTarget.Workbooks.Open(SummaryLocation)

        'set current worksheet
        xlsheetSource = xlWbSource.ActiveSheet
        xlsheetTarget = xlWbTarget.ActiveSheet

        'copy range of data from source to target file
        xlsheetSource.Range("A2:k" & IntAmountOfRows).Copy()
        xlsheetTarget.Range("A2:k" & intStartOfEmptyRow).PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)

        'Set focus on Summary ExcelSheet
        xlWbTarget = xlAppSource.Workbooks.Open(SummaryLocation)
        xlsheetSource = xlWbTarget.ActiveSheet

        'close excel
        xlWbSource.Close(True)
        xlWbTarget.Close(True)
        xlAppSource.Quit()
        xlAppTarget.Quit()

        'Cleanup
        xlAppSource = Nothing
        xlAppTarget = Nothing
        xlWbSource = Nothing
        xlWbTarget = Nothing
        xlsheetSource = Nothing
        xlsheetTarget = Nothing

    Next

However, when I execute the code, it throws the following error:

"Method PasteSpecial of Class Range has failed"

Wich points to the line:

xlsheetTarget.Range("A2:k" & intStartOfEmptyRow).PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)

I have no idea how to solve this, seeing as its a pretty general Error, googling it gives me next to none answers.

解决方案

I have found a way that does what I want, however I don't know WHY. So I will not accept my own answer, but wait for someone to explain it.

the code I used now is:

'copy range of data from source to target file
xlsheetSource.Range("A2:k" & IntAmountOfRows).Copy()
xlsheetTarget.Range("A" & intStartOfEmptyRow).PasteSpecial(Excel.XlPasteType.xlPasteValues)

这篇关于专业崩溃时粘贴excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！