Excel VBA宏：粘贴前检查内容（剪贴板？） [英] Excel VBA Macro: Check content (of clipboard?) before pasting

问题描述

我将数据从各种数据粘贴到Excel中有一些严重的问题。 Excel往往会变得聪明，做各种愚蠢的形式。我们需要数据作为文本。

I've had some serious issues with pasting data from various sources into Excel. Excel tends to try to be smart and does all kinds of silly formating. We need the data as text.

问题是我们有很多用户，其中许多用户对计算机的经验不是很高，所以要求他们使用右键单击和粘贴特殊每次都不是一个选项。

The problem is that we have a lot of users, and many of them are not very experienced with computers, so asking them to use right-click and 'Paste Special' every time is not an option.

我发现一个解决方案来记录使用粘贴特殊和文本的宏，并覆盖ctrl-v以使用功能。它似乎工作完美，直到我标记一个单元格，复制它，并尝试粘贴它。这个宏崩溃了。

I found a solution in recording a macro that uses 'Paste Special' and 'text', and overriding the ctrl-v to use this function. It seemed to work perfectly, until I marked a cell, copied it, and tried to paste it. The macro crashed.

所以我需要的是一个功能，可以检查我是否试图粘贴一些复制的文本，然后使用这一行：

So what I need is a function that can check if I am trying to paste some copied text, and then use this line:

 ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False

虽然如果我粘贴了一个标记的单元格，我想运行这一行（仅粘贴该值）：

While if I am pasting a marked cell, I want to run this line (to paste just the value):

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

我在编写Excel的VBA宏时并不是很有经验（我希望我永远不会有），所以如果有人有几个指针，我会非常感激。

I am not very experienced in writing VBA macros for Excel (and I hope I never have to be), so if anyone have a few pointers, I'd be most grateful.

推荐答案

对于剪贴板访问/操作，你需要添加一个参考Project-> References中的Microsoft Forms 2.0库。然后，您可以使用 MSForms.DataObject 类（其中包含） GetFormat 方法来检查剪贴板是否具有一种特定类型的数据。

For clipboard access/manipulation, you'll want to add a reference to the Microsoft Forms 2.0 library in Project->References. You can then use the MSForms.DataObject class that has (among others) a GetFormat method to check whether the clipboard has a particular type of data.

这个是使用 DataObject 的剪贴板处理的一个很好的介绍。

This is a pretty good intro to clipboard handling using DataObject.

这篇关于Excel VBA宏：粘贴前检查内容（剪贴板？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！