Option Explicit Const DEST = "D:\保存フォルダ" '保存先のフォルダ ■ここを変える Public Sub XlFileDownLoad() Const URL_PATTERN As String = "http://isin.krx.co.kr/jsp/BA_LT133_EXCEL.jsp?pg_no=1&wr_r_cd=&ef_iss_inst_cd={0}" Range("A2").Activate Do Until ActiveCell.Value = "" Dim cd As String cd = ActiveCell.Value Dim url As String url = Replace(URL_PATTERN, "{0}", cd) Dim fname As String fname = DEST & "\" & cd & ".xls" Call DL(url, fname) ActiveCell.Offset(1, 0).Activate Loop Range("A1").Activate MsgBox "終了", vbInformation End Sub Private Sub DL(ByVal url As String, ByVal fname As String) Const adTypeBinary = 1 Const adSaveCreateOverWrite = 2 Dim xmlHttp Set xmlHttp = CreateObject("Msxml2.XMLHTTP") xmlHttp.Open "GET", url, False xmlHttp.Send Dim stream Set stream = CreateObject("Adodb.stream") stream.Type = adTypeBinary stream.Open stream.Write xmlHttp.responseBody stream.Savetofile fname, adSaveCreateOverWrite Set stream = Nothing Set xmlHttp = Nothing End Sub
こちらでどうでしょう。
HALSPECIALさん、お恥ずかしい質問なのですが、標準モジュールではない場合、どこにペーストすればよろしいのでしょうか・・・ご教授いただけると助かります。