将剪贴板数据复制到数组
我必须使用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.