VBA错误1004-Range类的PasteSpecial方法 [英] VBA Error 1004 - PasteSpecial method of Range class

问题描述

最近我开始收到错误1004: PasteSpecial method of Range class failed.我知道人们以前曾说过，可能是试图在没有活动表的情况下将其粘贴到活动表，但是，正如您所看到的，所有内容都基于ThisWorkbook，所以这不应该成为问题.当Excel没有焦点时，这种情况发生得特别多.

Recently I started getting the error 1004: PasteSpecial method of Range class failed. I know people have said before that it might be that it is trying to paste to the active sheet when there is none, however, as you can see, everything is based on ThisWorkbook so that shouldn't be the problem. It happens extra much when Excel doesn't have the focus.

'references the Microsoft Forms Object Library
Sub SetGlobals()
    Set hwb = ThisWorkbook' home workbook
    Set mws = hwb.Worksheets("Code Numbers") ' main worksheet
    Set hws = hwb.Worksheets("Sheet3") ' home worksheet (Scratch pad)
    Set sws = hwb.Worksheets("Status") ' Status sheet
    Set aws = hwb.Worksheets("Addresses") ' Addresses sheet
End Sub
Sub Import()

    Call SetGlobals
    hws.Select
    'a bunch of code to do other stuff here.
    For Each itm In itms
        Set mitm = itm
        body = Replace(mitm.HTMLBody, "<img border=""0"" src=""http://www.simplevoicecenter.com/images/svc_st_logo.jpg"">", "")
        Call Buf.SetText(body)
        Call Buf.PutInClipboard
        Call hws.Cells(k, 1).Select
        Call hws.Cells(k, 1).PasteSpecial

        For Each shape In hws.Shapes
            shape.Delete
        Next shape

        'Some code to set the value of k 
        'and do a bunch of other stuff.
    Next itm
End Sub

更新:mitm和itm有两种不同的类型，所以我是为intellisense做的，还有谁知道.此代码获取电子邮件列表，并将其粘贴到excel中，以便excel解析html(包含表格)并将其直接粘贴到excel中.因此，数据直接进入工作表，我可以对其进行排序，解析以及其他所需的操作.

Update: mitm and itm have two different types, so I did it for intellisense and who knows what else. This code takes a list of emails and pastes them into excel so that excel parses the html (which contains tables) and pastes it directly into excel. Thus the data goes directly into the sheet and I can sort it and parse it and whatever else I want.

我想我基本上是在问一个除了将其放入html文件中以进行发布以外，还知道另一种方法的人.谢谢

I guess I'm basically asking for anyone who knows another way to do this besides putting it in an html file to post it. Thanks

推荐答案

这可能无法完全解决您的问题-但我注意到您的源代码中有一些内容太长而无法在注释中放置，因此这里是.当然，其中有些是因为您在示例中省略了它，但是为了防万一，我还是会提到它:

This probably will not exactly answer your problem - but I noticed a few things in your source code that are too long to place in a comment, so here it is. Some of it is certainly because you omitted it for the example, but I'll mention it anyway, just in case:

• 使用Option Explicit-这将避免很多错误，因为它会迫使您声明每个变量
• Call SetGlobals可以简化为SetGlobals-与Call Buf.SetText(body) = Bof.SetText Body等相同.
• 无需选择"任何内容-您可以直接通过工作表/范围/形状对象访问所有内容(这是最佳做法)，因此请不要选择(hws.Select，hws.Cells(k,1).Select)
• 为什么Set mitm = itm?因此mitm将与itm是同一对象-因此您可以简单地使用itm
• 您要多次删除hws中的所有形状-为itms中的每个元素.但是，一次就够了，因此将删除循环移到For Each循环之外
• 与其直接在剪贴板中粘贴内容，不如将其粘贴到单元格中，只需直接分配它即可:hws.Cells(k, 1).Value = body -这应该可以解决您的错误！
• 与其在"SetGlobals"中分配的工作表中不使用全局变量，只需简单地使用Excel本机提供的工作表对象:如果您在VBE中的右侧窗口中看到了项目树，则会看到工作表节点Sheet1 (sheetname) ，Sheet2 (sheetname)等.您可以重命名这些对象-转到其属性( F4 )并将其更改为有意义的名称-或当前名称(hwb，mws，.. .) 如果你想.然后，您可以在整个代码中访问它们，而无需进行任何分配！即使您将Sheet3的名称更改为有意义的名称，它也将在以后起作用！ ;-)

• Use Option Explicit - this will avoid a lot of errors as it forces you to declare every variable
• Call SetGlobals can be simplified to SetGlobals - same for Call Buf.SetText(body) = Bof.SetText Body, etc.
• No need to '.Select' anything - your accessing everything directly through the worksheet/range/shape objects (which is best practice), so don't select (hws.Select, hws.Cells(k,1).Select)
• Why Set mitm = itm? mitm will therefore be the same object as itm - so you can simply use itm
• You're deleteing all shapes in hwsmultiple times - for each element in itms. However, once is enough, so move the delete loop outside of the For Each loop
• Instead of putting something in the clipboard and then pasting it to a cell, just assign it directly: hws.Cells(k, 1).Value = body - this should solve your error!
• Instead of using global variables for worksheets that you assign in 'SetGlobals', simply use the sheet objects provided by Excel natively: If you look at the right window in the VBE with the project tree, you see worksheet nodes Sheet1 (sheetname), Sheet2 (sheetname), etc.. You can rename these objects - go to their properties (F4) and change it to meaningful names - or your current names (hwb, mws, ...) if you want. Then you can access them throughout your code without any assignment! And it'll work later, even if you change the name of Sheet3to something meaningful! ;-)

因此，考虑到所有这些，我最终得到以下代码，并执行相同的操作:

Thus, taking it all into account, I end up with the following code, doing the same thing:

Option Explicit

Sub Import()

    'a bunch of code to do other stuff here.

    For Each shape In hws.Shapes
        shape.Delete
    Next shape

    For Each itm In itms
        Call hws.Cells(k, 1) = Replace(itm.HTMLBody, "<img border=""0"" src=""http://www.simplevoicecenter.com/images/svc_st_logo.jpg"">", "")

        'Some code to set the value of k 
        'and do a bunch of other stuff.
    Next itm
End Sub

这篇关于VBA错误1004-Range类的PasteSpecial方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！