将剪贴板数据复制到数组 [英] Copy clipboard data to array

问题描述

我必须使用Ctrl A + Ctrl C从网页复制文本，以便在Excel中使用.

I have to to copy text, from a web page using Ctrl A + Ctrl C, to use in Excel.

复制的文本大约100行，大小不同.假设一行包含200个字符，下一行包含500个字符，第三行可能包含20个字符.

The copied text is about 100 lines with different sizes. Let us say one line has a string of 200 characters and the next one has 500 characters and the third maybe 20 characters.

有没有一种方法可以遍历剪贴板数据线并将其复制到数组中?

Is there a way to loop over the clipboard data lines and copy them to an array?

复制的文本样本(使用页面中的Ctrl A Ctrl C制成):
注意:我删除了一些行

Sample of the copied text (made with Ctrl A Ctrl C in the page):
Note : I removed some Lines

Usernames are XXXXXXXXXXXXXXXXX
DashboardAnalyticsPolicyAdministration
Web Insights
Print View
Start Over
1Select Chart Type
 Logs
Apply Filters
2Choose a Timeframe
Custom: 9/1/2015 12:00:00 AM - 9/30/2015 12:00:00 AM
3Select Filters
Add Filter
2.4 TB
2.0 TB
879.9 GB
656.8 GB
472.0 GB
442.4 GB
242.1 GB
213.5 GB
189.3 GB
103.8 GB
Office 365 - SSL Bypass
Professional Services
Streaming Media
Sites everyone
Internet Services
Corporate Marketing
Miscellaneous
Web Search
News and Media
Social Networking
URL CategoryTop 10TransactionsBytes

推荐答案

如果您遵循

To follow up on my comment, if you follow the instructions from here add a reference to Microsoft Forms Library 2.0 (under Tools/References in the VBA editor), the following function takes the contents of the clipboard and splits it into lines:

Function ClipToArray() As Variant
    Dim clip As New MSForms.DataObject
    Dim lines As String
    clip.GetFromClipboard
    lines = clip.GetText
    lines = Replace(lines, vbCr, "")
    ClipToArray = Split(lines, vbLf)
End Function

您可以像这样测试它:

Sub test()
    Dim A As Variant
    Dim i As Long
    A = ClipToArray()
    For i = LBound(A) To UBound(A)
        Debug.Print A(i)
    Next i
End Sub

然后我去了这个网站，复制了这首诗，然后运行了test.我在即时窗口中得到以下输出:

Then I went to this website and copied the poem and then ran test. I got the following output in the immediate window:

Some say the world will end in fire,
Some say in ice.
From what I've tasted of desire
I hold with those who favor fire.
But if it had to perish twice,
I think I know enough of hate
To say that for destruction ice
Is also great
And would suffice. 

这很好用，尽管您不必对从Internet复制的文本进行大量实验，然后再看到使用split进行的浅析浅析就可以了.

This worked nicely enough, although you don't have to run many experiments with text copied from the internet before you see that the superficial parsing using split leaves much to be desired.

这篇关于将剪贴板数据复制到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！