从web下载Zip文件(包含.csv)到excel VBA

Sub DownloadZipFile()
    Dim url As String
    Dim http As Object
    Dim response As Object
    Dim fileStream As Object
    Dim fileName As String
    
    ' 设置下载链接
    url = "http://example.com/path/to/file.zip"
    
    ' 创建HTTP请求对象
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' 发送GET请求
    http.Open "GET", url, False
    http.send
    
    ' 获取响应
    Set response = http.responseBody
    
    ' 保存文件
    fileName = "C:\path\to\save\file.zip"
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Type = 1 ' 二进制
    fileStream.Open
    fileStream.Write response
    fileStream.SaveToFile fileName, 2 ' 保存文件
    fileStream.Close
    
    ' 清理对象
    Set fileStream = Nothing
    Set response = Nothing
    Set http = Nothing
    
    MsgBox "Zip文件下载完成！"
End Sub

Sub UnzipFile()
    Dim zipFilePath As String
    Dim unzipFolderPath As String
    Dim shell As Object
    
    ' 设置Zip文件路径
    zipFilePath = "C:\path\to\save\file.zip"
    
    ' 设置解压缩文件夹路径
    unzipFolderPath = "C:\path\to\unzip\folder"
    
    ' 创建Shell对象
    Set shell = CreateObject("Shell.Application")
    
    ' 解压缩文件
    shell.Namespace(unzipFolderPath).CopyHere shell.Namespace(zipFilePath).Items
    
    ' 清理对象
    Set shell = Nothing
    
    MsgBox "Zip文件解压缩完成！"
End Sub

Sub ImportCSV()
    Dim csvFilePath As String
    Dim targetWorksheet As Worksheet
    
    ' 设置CSV文件路径
    csvFilePath = "C:\path\to\unzip\folder\file.csv"
    
    ' 设置目标工作表
    Set targetWorksheet = ThisWorkbook.Sheets("Sheet1")
    
    ' 导入CSV文件
    With targetWorksheet.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=targetWorksheet.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    
    MsgBox "CSV文件导入完成！"
End Sub