所以我编写了一些代码,通过模拟按钮点击从 twitter 下载分析数据。这不是漂亮的代码,但我现在可以找到它的全部工作。
我成功地点击了文件下载,之后会出现一个带有打开、保存选项的“框架通知栏”。我成功地单击打开两次,但是这是我遇到问题的地方。问题是我然后想与我刚刚选择打开的 CSV 文件中的数据进行交互,但是 CSV 文件直到代码完成运行后才存在。我知道必须有一个简单的解决方案,但我只是不知道要搜索什么。
我曾尝试使用 Wait 和 DoEvents 来查看是否有帮助,但到目前为止还没有运气。这是我的代码:
Private Sub CommandButton1_Click()
UserForm1.Hide
Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")
With appIE
.Navigate "https://analytics.twitter.com/user/QinetiQ/tweets"
.Visible = True
End With
Do While appIE.Busy
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:04"))
Set HTMLDoc = appIE.document
Set btn = HTMLDoc.getElementsByClassName("btn btn-default ladda-button")(0)
btn.Click
Application.Wait (Now + TimeValue("0:00:07"))
Application.SendKeys "%{S}"
Dim o As IUIAutomation
Dim e As IUIAutomationElement
Set o = New CUIAutomation
Dim h As Long
h = appIE.Hwnd
h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
If h = 0 Then Exit Sub
Set e = o.ElementFromHandle(ByVal h)
Dim iCnd As IUIAutomationCondition
Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open")
Dim Button As IUIAutomationElement
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%(o)"
Dim wb As Workbook
DoEvents
Application.Wait (Now + TimeValue("0:00:15"))
Set wb = GetWB
Dim ws As Worksheet
Set ws = wb.ActiveSheet
End Sub
Function GetWB() As Workbook
Dim wb As Workbook
wbName = "tweet"
For Each wb In Application.Workbooks
If wb.Name Like wbName & "*" Then
Set GetWB = wb
MsgBox ("Found it")
Exit Function
End If
Next wb
MsgBox ("failed to find worksheet")
End Function
我知道我使用了一些非常糟糕的技术并为此道歉。请问有谁可以帮忙,谢谢。
您可以使用将宏一分为二Application.OnTime
- 这将让您完成宏(以便 CSV 文件可以打开),然后安排一个新的宏在几秒钟后启动:
'This is the end of CommandButton1_Click
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%(o)"
Dim wb As Workbook
DoEvents
Application.OnTime Now()+TimeSerial(0,0,15), "ContinueDownloadMacro"
'In 15 seconds the "ContinueDownloadMacro" Sub will start
End Sub
Public Sub ContinueDownloadMacro()
Dim wb As Workbook, ws As Worksheet
Set wb = GetWB
Set ws = wb.ActiveSheet
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句