vba - 错误: Paste method of worksheet class failed

Question

我获得了一个通过从网站下载文本文件来检索数据的功能。

在 gop sub 中，当我调用该函数时，文件将打开，但是当我尝试将数据复制到工作表“数据”时，会发生错误:

Paste method of worksheet class failed.

有时这是另一个错误。

有人知道解决办法吗？

Sub gop()
Dim oWsBDR As Excel.Workbook

'On Error Resume Next

Set oWsBDR = WbBDR()
If oWsBDR Is Nothing Then
    MsgBox "Le fichier n'a pas été extrait."
Else
    'Set Workbooks("Task2").Worksheets("Data") = oWsBDR.Sheets("downloadFile")
    oWsBDR.Worksheets("downloadFile").Cells.Copy
    'Windows("Task2.xlsm").Cells(1, 1).Paste 'This doesn't work either
    Windows("Task2.xlsm").Cells(1, 1).PasteSpecial xlPasteValues 'Error
    Application.CutCopyMode = False
    With Worksheets("Data").Cells
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .ColumnWidth = 30
    End With
    Set oWsBDR = Nothing
End If

Application.DisplayAlerts = False
Windows("downloadFile.ln").Close

End Sub

Public Function WbBDR() As Excel.Workbook
Dim bOk As Boolean
Dim m_oME As Excel.Workbook
Dim m_oBDR As Excel.Workbook

If m_oBDR Is Nothing Then
    bOk = False
    Err.Clear
    On Error Resume Next
    Do Until bOk
        Workbooks.OpenText Filename:="https://.../starw/downloadFile.ln?fileName=bdr_ast_gop", _
            Origin:=xlMSDOS, _
            StartRow:=1, _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=True, _
            Comma:=False, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
                 Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
                 Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
                 Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _
                 Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _
                 Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _
                 Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), _
                 Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
                 Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), _
                 Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
                 Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1)), _
                 TrailingMinusNumbers:=True
        If Err.Number = 0 Then bOk = True
        Err.Clear
    Loop
    On Error GoTo 0
    Set m_oBDR = Application.Workbooks("downloadFile.ln")
End If
Set WbBDR = m_oBDR
End Function

Answer 1

您尚未指定工作表。也许您想要这样的东西:

Workbooks("Task2.xlsm").Worksheets("Data").Cells(1, 1).PasteSpecial xlPasteValues

<小时/>... 也使用这个(如下)是自找麻烦:

oWsBDR.Worksheets("downloadFile").Cells.Copy

快速解决方法是:

oWsBDR.Worksheets("downloadFile").UsedRange.Copy