【VBA】httpリクエストを用いて取得したCSVデータをExcelシートに形式を変えて記入する方法
Discussion
解決したいこと
VBAでWeb上のCSVデータをExcelのシートにEXcelの形式で貼付を行いたいです。
httpリクエストを利用して、Web上のCSVデータを取得しようと考えています。
しかし、httpリクエストでCSVデータを取得した後、Excelのシートにrange("A1")で
貼付を行うと、1つのセルの中にCSVデータが入力されてしまいます。
そこで、VBAでメモ帳を開いて、そこに一度CSVデータを貼付し、その後、Split関数を
用いてExcelに再度コピペをしようと考えました。
手順としましては、「CSVのテキストファイルを新たにC:のフォルダに作成する」→「作成したファイルを開く」→「開いたファイルにhttpリクエストで取得したCSVテキストデータを張り付ける」→「再度張り付けたCSVテキストデータを配列にいれて、Excelのシートに書き出す」を考えています。
現状、2点に関して教えて頂きたいです。
①エラーは表示されないが、CSVファイルが新たに作成されない。
②そもそもhttpリクエストで取得したCSVデータはどのようにして扱うのか？どこかのフォルダにも保存されていないようですし、あらたに作成したCSVファイルに貼り付けをしようとしても、ファイルの場所が不明なのでCSVの文字列をコピーもできない。
宜しくお願い致します。
発生している問題・エラー
ファイルがみつかりません。
例）
または、問題・エラーが起きている画像をここにドラッグアンドドロップ
VBA
ソースコードを入力
例）
Sub test2()
Dim httpReq As XMLHTTP60
Dim i As Long, j As Long
Dim strLine As String
Dim arrLine As Variant
Dim ws As Worksheet
Dim t As String
Dim fso As Object
Dim tso As Object
Dim strPath As String
Dim tFile As FileSystemObject
Call test3
Set httpReq = New XMLHTTP60
httpReq.Open "GET", "https://○○○reportFormat=CSV&○○○&maxIntradayDays=1&spanType=Intraday&startDateIntraday=2021%2F5%2F8&startHourIntraday=11&startMinuteIntraday=0&endDateIntraday=2021%2F5%2F8&endHourIntraday=12&endMinuteIntraday=0"
httpReq.send
Do While httpReq.readyState < 4
DoEvents
Loop
t = httpReq.responseText
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.GetFile("C:\Users\memo.txt").OpenAsTextStream(8).WriteLine("t")
.Close
End With
Set fso = Nothing
Open ("C:\Users\memo.txt") For Input As #1
i = 1
Do Until EOF(1)
Line Input #1, strLine
arrLine = Split(strLine, ",")
For j = 0 To UBound(arrLine)
Worksheets("Paste01").Cells(i, j + 1).Value = arrLine(j)
Next j
i = i + 1
Loop
Close #1
Set tFso = Nothing
Set tFile = Nothing
Set httpReq = Nothing
End Sub
Sub test3()
On Error Resume Next
Dim fso As New FileSystemObject '// FileSystemObjectクラス
Dim ts As TextStream '// TextStreamクラス
Dim sFilePath '// ファイルパス
'// ファイルパスを指定
sFilePath = "C:\Users\memo.txt"
'// ファイルを作成
Set ts = fso.CreateTextFile(Filename:=sFilePath, Overwrite:=True, Unicode:=False)
'// ファイルを閉じる
ts.Close
'// エラー発生時
If Err.Number <> 0 Then
'// エラー内容を出力
Debug.Print Err.Number & " " & Err.Description
End If
End Sub
自分で試したこと
上記の方法とは別で、ハイパーリンクでCSVが入ったWebのURLを開き、開いた段階でC:のユーザーのフォルダ内に
CSVファイルがExcelの形式で保存されます。なのでURLを開く前にC:内のCSVのデータを全て消去して、URLをひらき、自動で
ファイルに保存されたCSVデータをひらくという流れのVBAを作成しました。
しかし、フォルダパスを指定して開く際にユーザーに保存される為、ユーザー名をワイルドカードで作成しましたが参照のファイルが見つからないというエラーで保存先のCSVファイルを開くことが出来ず、断念しました。
