Excel VBA（マクロ）ネット上のリンクをクリックしてから生成されるCSVファイルを取得するには？

Question

Excel VBA（マクロ）ネット上のリンクをクリックしてから生成されるCSVファイルを取得するには？

下記のページから1年分のNYダウのデータを取得し、エクセルのシートに貼り付けたいと思っています。
https://stooq.com/q/d/?s=^dji

ブラウザから手動でアクセスした場合、

フォームの「Start date:」と「End date:」を1年分の日付を入力し、「Show」ボタンを押す。その後、
ページの下の方にある「Download data in csv file...」をクリックすると、CSVファイルが生成されてダウンロードできるのですが、
この作業をExcelマクロで行いたいと思っています。

発生している問題

CSVファイルのアドレスがわかっていれば取り込むことはできるのですが、
フォーム送信のあと、生成されるCSVファイルの取り込み方法がわかりません。

試したこと

ちなみに「Show」ボタンを押した後「Download data in csv file...」に生成されるリンクアドレスは、2019年4月23日から1年間のデータが欲しい場合、
https://stooq.com/q/d/l/?s=^dji&d1=20180423&d2=20190423&i=d
と、なります。

ならば、マクロの記録をしながら、webクエリで取り込めばいいのでは？と思い実行すると、ファイルのダウンロードのウィンドウが出て、ファイルを保存することはできるのですが、開くを選択すると「選択したコマンドは、このショートカット　メニューからは実行できません。」となり、直接取り込むことはできませんでした。

現状…

いったんローカル、ダウンロードフォルダなどに保存し、読み込むしかないのでしょうか？できれば一度でシートに展開できればありがたいのですが。。。
皆さまのお知恵をおかしください。

Answer 1

直接シートに取り込めるようになるまで、
一旦ダウンロードしてCSVファイルで保存。
それから取り込む仕様にしたいと思います。

WorkBooks.openのファイル名にはURLが直接指定できます。
つまり

   Call Workbooks.Open("https://stooq.com/q/d/l/?s=^dji&d1=20180423&d2=20190423&i=d", 0, True)

などとすれば、workのbookに直接CSVを読み込むことができます。

Comments

[kawase2]

お答え、ありがとうございます。

＞workのbookに直接CSVを読み込むことができます。

直接読み込むというよりは、新しいブックが作成されて、そこに読み込まれるのですね！
このお答えを参考に下記のコードを書いてみましたら、
NYダウのデータから欲しい部分（直近○○日分とか）をピックアップできて、やりたいことができました。

参考までに、私の書いたコードを載せておきます。
（幼稚なコードですみません(*ﾟ∀ﾟ*)　）

Sub Test01()
'NYダウの最新データ取得
Dim wb1 As Workbook
Dim d1, d2, d3

'最新の日付を取得
d1 = (Year(Date) - 1)
d2 = Year(Date)
d3 = Format(Date, "mm") & Format(Date, "dd")

'URLを設定して新しいブックに開く
Call Workbooks.Open("https://stooq.com/q/d/l/?s=^dji&d1=" & d1 & d3 & "&d2=" & d2 & d3 & "&i=d", 0, True)
Set wb1 = ActiveWorkbook

'直近200日分の日付と終値のデータをコピー
With wb1.Worksheets(1)
a = .Cells(Rows.Count, 1).End(xlUp).Row 'ラストの行数を調べる
.Range(Cells(a - 199, 1), Cells(a, 1)).Copy ThisWorkbook.Sheets(1).Range("A1")
.Range(Cells(a - 199, 5), Cells(a, 5)).Copy ThisWorkbook.Sheets(1).Range("B1")
End With

'ブックを閉じる
Application.DisplayAlerts = False
wb1.Close
Application.DisplayAlerts = True

End Sub

Answer 2

Windows API関数を用いてファイルをダウンロードしてみました。
ご参考まで。

#If Mac Then
    'Running on MacIntosh. Do nothing.
#ElseIf VBA7 Then
    'Excel 2010 or later:
     #If Win64 Then
        '64-bit Excel, 2010 or later:
        'Use PtrSafe, LongPtr, and LongLong:
            Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
                                     Alias "URLDownloadToFileA" _
                                    (ByVal pCaller As LongPtr, _
                                     ByVal szURL As String, _
                                     ByVal szFileName As String, _
                                     ByVal dwReserved As LongLong, _
                                     ByVal lpfnCB As LongPtr) As LongLong
     #Else
        '32-bit Excel 2010 or later:
        'Use PtrSafe and LongPtr, but NOT LongLong:
            Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
                                    Alias "URLDownloadToFileA" _
                                    (ByVal pCaller As LongPtr, _
                                    ByVal szURL As String, _
                                    ByVal szFileName As String, _
                                    ByVal dwReserved As Long, _
                                    ByVal lpfnCB As LongPtr) As Long
     #End If
#Else
    'Excel 2007 or earlier:
        Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
          "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
            szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub Get_CSV()

    Const strURL = "https://stooq.com/q/d/l/?s=^dji&d1=20180423&d2=20190423&i=d"
    Dim strFNAME As String  'ダウンロード先（パス＋ファイル名）
    Dim ReturnValue As Variant

    'ファイル名をブックのパス＋test.csvとする
    strFNAME = ThisWorkbook.Path & "\test.csv"

    'URLDownloadToFile API をコールする
    ReturnValue = URLDownloadToFile(0, strURL, strFNAME, 0, 0)

    If ReturnValue = 0 Then 'ダウンロードに成功すると「ReturnValue =0」となる。
        Debug.Print "ダウンロード成功"
    Else
        Debug.Print "ダウンロード失敗"
    End If

    '結果の表示
    MsgBox strFNAME & "を保存しました。"

End Sub

Comments

[kawase2]

ご回答ありがとうございます。
直接シートに取り込めるようになるまで、
一旦ダウンロードしてCSVファイルで保存。
それから取り込む仕様にしたいと思います。