使用excel vba从网站下载zip文件（如果还能从zip文件中提取csv并在excel中打开它，那就更好了）

after quite some searching I've not been able to make a macro that would download a .zip file from a specific website. I mean I've been able to find similar problems but have not been able to apply the changes necessary in order for my problem to be solved. The website that contains the zip files is: https://nio.gov.si/nio/data/prvic+registrirana+vozila+v+letu+2014+po+mesecih, under table header "Priponke" are the files. For example: December 2014 (959 kb), November 2014 (1061 kb), ... The url that downloads the zip file for December 2014 is "cms/download/document/a7605005b6879fe5f7dbab6d60d4ae787dbced6b-1422453741279". I thank you in advance and am awaiting your reply.

经过一番搜索,我无法制作一个可以从特定网站下载.zip文件的宏。我的意思是我已经能够找到类似的问题但是无法应用必要的更改以便解决我的问题。包含zip文件的网站是:https://nio.gov.si/nio/data/prvic+registrirana+vozila+v+letu+2014+po+mesecih,在表头“Priponke”下面是文件。例如:2014年12月(959 kb),2014年11月(1061 kb),... 2014年12月下载zip文件的网址是“cms / download / document / a7605005b6879fe5f7dbab6d60d4ae787dbced6b-1422453741279”。我提前感谢你,等待你的回复。

My current code is:

我目前的代码是:

Public Sub DownloadFile()
    Dim objWHTTP As Object
    Dim strPath As String
    Dim arrData() As Byte
    Dim lngFreeFile As Long

On Error Resume Next
    Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
    If Err.Number <> 0 Then
        Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
    End If
On Error GoTo 0

    strPath = "https://nio.gov.si/nio/data/prvic+registrirana+vozila+v+letu+2014+po+mesecih"
    strPath = "https://nio.gov.si/nio/cms/download/document/a7605005b6879fe5f7dbab6d60d4ae787dbced6b-1422453741279"

    objWHTTP.Open "GET", strPath, False
    objWHTTP.send
    arrData = objWHTTP.responseBody

    If Len(Dir("C:\FootieFile", vbDirectory)) = 0 Then
        MkDir "C:\FootieFile"
    End If

    lngFreeFile = FreeFile
    Open "C:\FootieFile\MyFile.xml" For Binary Access Write As #lngFreeFile
        Put #lngFreeFile, 1, arrData
    Close #lngFreeFile

    Set objWHTTP = Nothing
    Erase arrData
End Sub

Kind regards

1 个解决方案

#1

You need to download a binary file, and save it. This can be done using the MSXML2.XMLHTTP60 object to download, and ADODB.Stream object for saving.

您需要下载二进制文件并保存。这可以使用MSXML2.XMLHTTP60对象进行下载,并使用ADODB.Stream对象进行保存。

See e.g. http://www.motobit.com/tips/detpg_read-write-binary-files/

参见例如http://www.motobit.com/tips/detpg_read-write-binary-files/

I've used this succesfully to download JPG files from a server and display them in an MS Access front end.

我成功地使用它从服务器下载JPG文件并在MS Access前端显示它们。

Also I hope you realize you can't start your url with 'cms', as you will need the fully qualified domain name plus resource (aka http:// etc)

此外,我希望你意识到你不能用'cms'开始你的网址,因为你需要完全合格的域名加资源(又名http:// etc)

Also be careful with unicode data. For that you might need to use StrConv(). Check after downloading that the size of your file is the same as it is on the server.

还要小心unicode数据。为此,您可能需要使用StrConv()。下载后检查文件大小是否与服务器上的大小相同。

#1