コード掲載の体裁テストです深い意味はなっしんぐ
EXCELで１セル書き換えでも300銘柄制限は突破できない事の検証
Sub Test1()
Dim row As Integer: row = 1
Dim code As Integer: For code = 1300 To 3000
For Each T In Array("T", "Q", "OS", "OJ")
Dim rss As String: rss = "=RSS|'[Code].[T]'!銘柄名称"
rss = Replace(rss, "[Code]", CStr(code))
rss = Replace(rss, "[T]", T)
Cells(1, 1).Value = code
Cells(1, 2).Formula = rss
DoEvents
If Cells(1, 2).Text <> "#N/A" Then
Debug.Print row, code, T, Cells(1, 2).Value
row = row + 1
Exit For
End If
Next
Next
End Sub
みごとに299銘柄で止まりますってこと
お次はVB6で300銘柄制限を突破してみる。
標準機能のDDELinkを利用してみますね。
Private Sub Form_Load()
Dim count As Integer: count = 0
Dim code As Integer: For code = 1300 To 3000
For Each T In Array("T", "Q", "OS", "OJ")
Dim topic As String
topic = "RSS|[Code].[T]"
topic = Replace(topic, "[Code]", CStr(code))
topic = Replace(topic, "[T]", T)
On Error Resume Next
Label1.LinkMode = 0 '0.なし
Label1.LinkTopic = topic
Label1.LinkItem = "銘柄名称"
Label1.LinkMode = 1 '1.自動
If Err.Number = 0 Then
count = count + 1
Debug.Print count, code, T, Label1.Caption
Exit For
End If
DoEvents
Next
Next
End Sub
遅いね。でも一応300銘柄以上回ります。
ちなみにDDEは２０年近く昔の技術なので今のVB2005(VB.NET)にはDDELink機能はもうありませんよ。と
で、VB2005(VB.NET)にはDDE機能はありませんが、NDdeなどの既存コンポーネントを利用してお手軽に同様の操作が可能です。
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim enc As Encoding = Encoding.GetEncoding("Shift-JIS")
Dim count As Integer = 0
For code As Integer = 1300 To 3000
For Each T As String In New String() {"T", "Q", "OS", "OJ"}
Dim topic As String = "[Code].[T]"
topic = Replace(topic, "[Code]", CStr(code))
topic = Replace(topic, "[T]", T)
Dim item As String = "銘柄名称"
Using client As New DdeClient("RSS", topic)
client.Connect()
Dim buf(1024) As Byte
If client.TryRequest(item, 1, 60000, buf) = 0 Then
count = count + 1
Dim s As String = enc.GetString(buf)
s = s.Substring(0, s.Length - 1)
Debug.WriteLine(count & vbTab & code & vbTab & T & vbTab & s)
End If
End Using
Next
Next
End Sub
リクエスト方式だと遅いですがお手軽に全銘柄取得できます。
とりあえず入門用ってことでAPI直接呼出しとかコールバックとかはまた別のお話。
AccessでDDE
Sub Test1()
Dim count As Integer: count = 0
Dim code As Integer: For code = 1300 To 3000
For Each T In Array("T", "Q", "OS", "OJ")
Dim topic As String
topic = "[Code].[T]"
topic = Replace(topic, "[Code]", CStr(code))
topic = Replace(topic, "[T]", T)
On Error Resume Next
Dim ChanNum As Long: ChanNum = Application.DDEInitiate("RSS", topic)
Dim s As String: s = Application.DDERequest(ChanNum, "銘柄名称")
If Err.Number = 0 Then
Application.DDETerminate ChanNum
count = count + 1
Debug.Print count, code, T, s
Exit For
End If
Application.DDETerminate ChanNum
DoEvents
Next
Next
End Sub
最近のコメント
sippofactory at 07.22
amits at 07.21
sippofactory at 07.21
sippofactory at 07.21
sippofactory at 06.27
匿名 at 06.23
sippofactory at 06.08
sippofactory at 05.26
sippofactory at 05.05