从网站复制/粘贴的 VBA 自动化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13042624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA automation to copy/paste from websites
提问by SCar88
OK. So here's my thought process of how this code should work. I have a sheet that has a list of web addresses that I would like to use the for loop to iterate through. For each web address I'd like to copy and paste the whole web page to a temporary sheet in the workbook. After the page is pasted into Excel(2010) I would do some formatting. Only copy and paste what I want from the temporary sheet to a master sheet. The next link is then navigated in Internet Explorer, and like before the web page is pasted to the temp sheet and then what I need is appended to the master sheet. I'm experiencing the following issues:
好的。所以这是我对这段代码应该如何工作的思考过程。我有一个工作表，其中包含我想使用 for 循环进行迭代的网址列表。对于每个网址，我想将整个网页复制并粘贴到工作簿中的临时工作表中。将页面粘贴到 Excel(2010) 后，我会进行一些格式化。只将临时工作表中我想要的内容复制并粘贴到母版工作表中。然后在 Internet Explorer 中导航下一个链接，就像在将网页粘贴到临时表之前一样，然后将我需要的内容附加到母版表。我遇到以下问题：
1) No matter what worksheet I declare for the web page to paste to it always pastes to the first sheet. This happens to be the sheet that I have my command button on, but that shouldn't make a difference. Should it?
1）无论我为网页声明什么工作表粘贴到它总是粘贴到第一张工作表。这恰好是我的命令按钮所在的工作表，但这应该没有区别。应该是？
2) The appending to the master sheet is not working. When the macro is finished running the only records that appear to have been pasted to the master sheet is from the last web page.
2）附加到主表不起作用。当宏完成运行时，似乎已粘贴到母版表的唯一记录来自最后一个网页。
I'm thinking that maybe what I need to do is add a pause of a certain amount of time between selecting/copying/pasting the web page to Excel to fix issue #2. Help with either of these issues would be greatly appreciated.
我在想，也许我需要做的是在选择/复制/粘贴网页到 Excel 之间添加一定时间的暂停以解决问题 #2。对这些问题中的任何一个问题的帮助将不胜感激。
Private Sub CommandButton1_Click()
Dim IE As Object
startTime = Now()
Set IE = CreateObject("InternetExplorer.Application")
For rows1 = 2 To 11
For columns1 = 2 To 2
strLink = ThisWorkbook.Sheets("links").Cells(rows1, columns1)
With IE
.Visible = True
.Navigate strLink
Do While IE.ReadyState <> 4
DoEvents
Loop
IE.ExecWB 17, 0
IE.ExecWB 12, 2
ThisWorkbook.Sheets("temp").Paste Range("A1")
End With
'Copy/paste and format from temp to master sheet
nextRow = ThisWorkbook.Sheets("master").Range("A1").End(xlDown).Row + 1
ThisWorkbook.Sheets("temp").Range("A173:S247").Copy
ThisWorkbook.Sheets("Master").Range("A" & nextRow).PasteSpecial Paste:=xlPasteValues
Next columns1
Next rows1
endTime = Now()
MsgBox ("Done running. It took from " & startTime & " to " & endTime & ".")
End Sub
回答by ExactaBox
Issue 1: your code ThisWorkbook.Sheets("temp").Paste Range("A1")will always refer to the active sheet -- Range("A1") is implied to be using the active sheet since there is no defined sheet object. You need to reference the correct sheet in the argument of the function. So try ThisWorkbook.Sheets("temp").Paste ThisWorkbook.Sheets("othersheet").Range("A1")if you want the screen scrape pasted to something other than "temp"
问题 1：您的代码ThisWorkbook.Sheets("temp").Paste Range("A1")将始终引用活动工作表 -- Range("A1") 暗示使用活动工作表，因为没有定义的工作表对象。您需要在函数的参数中引用正确的工作表。因此，ThisWorkbook.Sheets("temp").Paste ThisWorkbook.Sheets("othersheet").Range("A1")如果您希望将屏幕刮擦粘贴到“temp”以外的其他内容，请尝试
(your code will be much more readable if you set ThisWorkbook and each Sheet to a variable)
（如果您将 ThisWorkbook 和每个 Sheet 设置为变量，您的代码将更具可读性）
Issue 2: You are looking to paste after the last row on the Master sheet. Lose the first line of that section, keep the second, the replace the third with
问题 2：您希望在母版工作表的最后一行之后粘贴。丢失该部分的第一行，保留第二行，将第三行替换为
ThisWorkbook.Sheets("Master").Cells(ThisWorkbook.Sheets("Master").UsedRange.Rows.Count + 2, 1).PasteSpecial Paste:=xlPasteValues
(again, you should use variables to represent workbook and worksheet objects to make your code cleaner)
（同样，您应该使用变量来表示工作簿和工作表对象，以使您的代码更清晰）
