我是VBA的新手,正在尝试学习一些功能。 我创建的是 excel 中的代码,它连接到 API 并将数据拉取到表中。 我需要它在指定的时间段(上午 9 点至下午 4 点)循环并每 30 秒刷新一次。 我的问题是我不知道如何做到这一点。 这是我的代码请帮忙! 谢谢!!
此外,正在填充的表设置在以下列中: "符号" "名称" "要价" "出价" "价格" "天数范围" "1年目标价格" "交易量" "平均每日交易量"
我的代码:
Private Sub BTN_Start_Click()
Dim W As Worksheet: Set W = ActiveSheet
Dim Last As Integer: Last = W.Range("A10000").End(xlUp).Row
If Last = 1 Then Exit Sub
Dim Symbol As String
Dim i As Integer
For i = 2 To Last
Symbol = Symbol & W.Range("A" & i).Value & "+"
Next i
Symbol = Left(Symbol, Len(Symbol) - 1)
Dim url As String: url = "http://finance.yahoo.com/d/quotes.cvs?s=" & Symbol & "&f=snb2b3k1m2t8va2"
Dim Http As New winhttprequest
Http.Open "GET", url, False
Http.send
Dim Resp As String: Resp = Http.ResponseText
Dim Lines As Variant: Lines = Split(Resp, vbNewLine)
Dim sLine As String
Dim Values As Variant
For i = 0 To UBound(Lines)
sLine = Lines(i)
If InStr(sLine, ",") > 0 Then
Values = Split(sLine, ",")
W.Cells(i + 2, 2).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0)
W.Cells(i + 2, 3).Value = Values(UBound(Values) - 6)
W.Cells(i + 2, 4).Value = Values(UBound(Values) - 5)
W.Cells(i + 2, 5).Value = Values(UBound(Values) - 4)
W.Cells(i + 2, 6).Value = Values(UBound(Values) - 3)
W.Cells(i + 2, 7).Value = Values(UBound(Values) - 2)
W.Cells(i + 2, 8).Value = Values(UBound(Values) - 1)
W.Cells(i + 2, 9).Value = Values(UBound(Values))
End If
Next i
W.Cells.Columns.AutoFit
End Sub
您可以将代码的主要部分放在此循环中。 在时间结束之前,它不会退出。
Do while timevalue(now()) > #9:00:00# and timevalue(now()) < #16:00:00#
'do stuff
Application.Wait(Now + #0:00:30#)
loop
在当前代码的开头放置Call Timer
然后将其包含在另一个子中:
Sub Timer()
Dim CountDown As Date
CountDown = Now + TimeValue("00:00:30")
Application.OnTime CountDown, "BTN_Start_Click"
End Sub
这将从您启动代码时起每 30 秒运行一次代码。
如果您只想单击它并离开它,这是另一种选择......
Sub Timer()
If TimeValue(CStr(Now)) >= TimeValue("9:00:00 AM") And TimeValue(CStr(Now)) <= TimeValue("4:00:00 PM") Then
Dim CountDown As Date
CountDown = Now + TimeValue("00:00:30")
Application.OnTime CountDown, "BTN_Start_Click"
Else
Dim CountTWO As Date
CountTWO = Now + TimeValue("00:00:05")
Application.OnTime CountTWO, "Timer"
End If
End Sub
Sub BTN_Start_Click()
Call Timer
'Your code here
End Sub
这将检查以确保时间在上午 9 点到下午 4 点之间......然后每三十秒它会再次检查。如果在上午 9 点到下午 4 点之间,那么它将运行您的代码。