从链接下载 .csv [英] Download .csv from a link

查看:69
本文介绍了从链接下载 .csv的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试构建一个 VBA 代码,该代码将此日历作为输入:
https://www.fxstreet.com/economic-calendar#

I'm trying to build a VBA code which has as input this calendar:
https://www.fxstreet.com/economic-calendar#

在此链接中,可以选择以 .csv 格式下载.例如,这是下载链接.https://calendar.fxstreet.com/eventdate/?f=csv&v=2&timezone=Central+标准+时间&rows=&view=range&start=20180909&end=20180915&countrycode=US&volatility=0&culture=en&columns=CountryCurrency%2CCountdown

In this link there exists the option to download it in format .csv. For example this was the link of the download. https://calendar.fxstreet.com/eventdate/?f=csv&v=2&timezone=Central+Standard+Time&rows=&view=range&start=20180909&end=20180915&countrycode=US&volatility=0&culture=en&columns=CountryCurrency%2CCountdown

我想根据它在 VBA 中定义一个代码,根据我在单元格A1"中的输入更改开始日期和结束日期;和A2",但由于链接的结构(它不在 .csv 中完成),这是不可能的.如果您在浏览器中转到下载部分,然后按链接,它将不会再次下载,而是会出现一条错误消息.它只在打开第一个链接并选择下载选项时有效 - 所以,我无法在 VBA 中基于它构建结构.

I want to define a code in VBA based on it, changing that start date and end date according to my input in cell "A1" and "A2", but it's impossible due to the structure of the link (it doesn't finish in .csv). If you go to section of downloads in your browser, and press the link, it won't download again, instead a message of error will appear. It just works when opening the first link and selecting the option to download- so, I can´t build a structure in VBA based on it.

有没有办法让 VBA 可以打开链接然后选择"?下载选项,或者您有其他想法使用 VBA 下载吗?

Does there exist a way that VBA can open the link and then "select" the option to download, or do you have another idea to download it using VBA?

推荐答案

将 'iTable' 变量调整为要导入的表号(即 1、2、3 等)

Adjust the 'iTable' variable to the table number that you want to import (i.e., 1, 2, 3, etc)

Sub HTML_Table_To_Excel()

Dim htm As Object
Dim Tr As Object
Dim Td As Object
Dim Tab1 As Object

'Replace the URL of the webpage that you want to download
'Web_URL = "https://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population"
Web_URL = "https://www.fxstreet.com/economic-calendar"

'Create HTMLFile Object
Set HTML_Content = CreateObject("htmlfile")

'Get the WebPage Content to HTMLFile Object
With CreateObject("msxml2.xmlhttp")
.Open "GET", Web_URL, False
.send
HTML_Content.body.innerHTML = .responseText 'this is the highlighted part for the error
End With
Column_Num_To_Start = 1
iRow = 1
iCol = 1
iTable = 1

'Loop Through Each Table and Download it to Excel in Proper Format
For Each Tab1 In HTML_Content.getElementsByTagName("table")
    With HTML_Content.getElementsByTagName("table")(iTable)
        For Each Tr In .Rows
            For Each Td In Tr.Cells
            Worksheets("Sheet1").Cells(iRow, iCol).Select
            Worksheets("Sheet1").Cells(iRow, iCol) = Td.innerText
            iCol = iCol + 1
            Next Td
        iCol = Column_Num_To_Start
        iRow = iRow + 1
        Next Tr
    End With

Next Tab1

MsgBox "Process Completed"
End Sub

这篇关于从链接下载 .csv的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆