①CSVファイルが新たに作成されない。
自分で試したことに書かれている手順だとURLを開いたタイミングでwebブラウザが(リクエストを実行)開き、webブラウザが処理できない形式のファイル(csv)を自動的にダウンロードしているのだと思います。
VBA(Excelマクロで使用されている言語)などのプログラム言語でリクエストを実行した場合は言語側で処理が可能なので新たにダウンロードされることはありません。
意図的にプログラムからIEのアプリケーションなどを使用してhttpリクエストを実行した場合はダウンロードする動きをするかもしれませんが...
②そもそもhttpリクエストで取得したCSVデータはどのようにして扱うのか?
後述のコードにてコメント書きました
Excelのシートにrange("A1")で貼付を行うと、1つのセルの中にCSVデータが入力されてしまいます。
Rangeは読んで字のごとくなのですが範囲選択して使用するメソッドです。
なのでRange("A1:C6")等の範囲を選択して使用します。
range("A1")とした場合はA1の範囲を選択しているためA1にしか値を入れることができません。
後述コードではCellsで書き出しを行っています。
ある種の代替コードになってしまいますが以下のような実装はどうでしょう?
[処理内容]
1.CSVのデータをhttpリクエストで取得してくる
2.取得したデータを分割しながらシートに出力する
Option Explicit '変数宣言の強制
Sub TEST()
Dim ws As Worksheet '出力先のシート
Set ws = Worksheets("Sheet1")
'Microsoft XML, v6.0の参照設定が必要
Dim httpReq As XMLHTTP60
'データの取得
Set httpReq = New XMLHTTP60
httpReq.Open "GET", "http://XXXXXXX//YYYYYY.csv"
httpReq.send
'リクエストが終了するまで待機
Do While httpReq.readyState < 4
DoEvents
Loop
'②の回答
'httpReq.responseTextでhttpリクエストで取得した内容が取り出せます。
'htmlを取得した場合htmlのコードが表示されます
'取得結果を改行コード単位に分割する
Dim line
line = Split(httpReq.responseText, vbCrLf)
'ループ内で使う変数群
Dim csList '一行の内容をカンマで分割したものを格納する配列
Dim indexA, indexB 'ループカウンタ
Dim gyouIndex, retuIndex '行と列のインデックス
'インデックス初期化
gyouIndex = 1
retuIndex = 1
' メモ:分割した配列の長さを取得する関数 -> UBound
'各行を取り出しながら処理する
For indexA = 0 To UBound(line)
'ログ表示
Debug.Print "処理対象の行 >>>" & line(indexA)
'行の内容をカンマ単位に分割する
csList = Split(line(indexA), ",")
'カンマで分割したものを取り出しながら処理する
For indexB = 0 To UBound(csList)
'ログ表示
Debug.Print "処理対象の要素 >>>" & csList(indexB)
'ワークシートに出力する
ws.Cells(gyouIndex, retuIndex).Value = csList(indexB)
'出力の列数をカウントアップする
retuIndex = retuIndex + 1
Next indexB
'出力の列数を初期化する
retuIndex = 1
'出力の行数をカウントアップする
gyouIndex = gyouIndex + 1
Next indexA
End Sub