Excel VBA-从剪贴板粘贴文本(错误1004) [英] Excel VBA - paste text from clipboard (error 1004)

问题描述

有人可以帮助我提供此代码吗?

can someone help me with this code?

Sub TEST()
Dim Val As Variant
Sheets("Sheet 3").Select
Val = Range("A2").Value
Sheets("Sheet 1").Select
Range("AY" & Val).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Sheet 3").Select
Application.CutCopyMode = False
End Sub

我必须将简单的数字从网上转移到excel.我需要将号码从网上复制到剪贴板中，然后转到excel并运行Macro.此宏应转到第3页"，根据A2值设置"Val"，转到第1页，在AY&中选择范围."Val"并从剪贴板粘贴到此单元格数据(数字).

I have to transfer simple number from web to excel. I need to copy number from web into a clipboard, then go to excel and run Macro. This macro should go to "sheet 3" set "Val" based on A2 value, go to sheet 1, select range in AY & "Val" and paste to this cell data (the number) from clipboard.

但是当宏到达第7行(Selection.PasteSpecial)时，我遇到了错误:

But when macro reach line 7 (Selection.PasteSpecial) Im getting Error:

运行时错误"1004":Range类的PasteSpecial方法失败

Run-time error '1004': PasteSpecial method of Range class failed

有错误的地方，请:)

Where I have the bug, please :)

推荐答案

您必须使用MSForms.DataObject与剪贴板进行交互:

You must use a MSForms.DataObject to interact with the clipboard:

Sub TextFromClipboard()
'This works only with text!
  Dim oData As Object

  'New MSForms.DataObject with guid and late binding
  Set oData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  'Get text from clipboard
  'to the DataObject
  oData.GetFromClipboard

  'Show text
  MsgBox oData.GetText
End Sub

要将文本放置到剪贴板中，可以使用以下两种方法:

To put text to clipboard you can use the following 2 methods:

oData.SetText sText
oData.PutInClipboard

这篇关于Excel VBA-从剪贴板粘贴文本(错误1004)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！