2191528

くぴんのブログ

ETF140銘柄チャート画像取得マクロ

Sub ETF()
Dim i As Integer, j As Integer, k As Integer
Dim code As String
Dim name As String

For i = 1 To 47
For j = 1 To 3
Worksheets("ETF121014").Activate
With Worksheets("ETF121014")
k = k + 1
If Cells(k + 1, 2) = "大証" Then
code = Cells(k + 1, 1) & ".O"
End If
If Cells(k + 1, 2) = "東証" Then
code = Cells(k + 1, 1) & ".T"
End If
End With
name = Cells(k + 1, 3)
If name = "" Then End
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Cells(1 + 13 * (i - 1), 1 + 5 * (j - 1)) = code
Worksheets("Sheet1").Cells(1 + 13 * (i - 1), 2 + 5 * (j - 1)) = name
Worksheets("Sheet1").Cells(2 + 13 * (i - 1), 1 + 5 * (j - 1)).Select

'ダウンロードした画像を今のセルの位置に挿入します。
Worksheets("Sheet1").Pictures.Insert Filename:="http://gchart.yahoo.co.jp/z?s=" & code & "&t=1y&q=c&l=off&z=m&p=m65,m130,s&a=v&c="
ActiveSheet.Shapes(k).Select '画像選択
Selection.Height = Selection.Height * 0.7 '70%に縮小
Selection.Width = Selection.Width * 0.7 '70%に縮小
Next j
Next i

End Sub

ジャンル別一覧