从 VBA (Excel) 中异步下载文件 [英] Asynchronous File Downloads from Within VBA (Excel)

问题描述

我已经尝试使用许多不同的技术来解决这个问题......一种工作得很好但在运行时仍然绑定代码的方法是使用 api 调用:

I've already tried using many different techniques with this... One that works pretty nicely but still ties up code when running is using the api call:

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

和

IF URLDownloadToFile(0, "URL", "FilePath", 0, 0) Then
End If

我还使用(成功)代码从 Excel 中编写了 vbscript，然后使用它运行 wscript 并等待回调.但这又不是完全异步的，仍然会占用一些代码.

I've also used (Successfully) code to write vbscript from within Excel and then running with it wscript and waiting for the callback. But again this isn't totally async and still ties up some of the code.

我希望在事件驱动类中下载文件，并且 VBA 代码可以使用DoEvents"在一个大循环中执行其他操作.当一个文件完成时，它可以触发一个标志，代码可以在等待另一个文件的同时处理该文件.

I'd like to have the files download in an event driven class and the VBA code can do other things in a big loop with "DoEvents". When one file is done it can trigger a flag and the code can process that file while waiting for another.

这是从 Intranet 站点中提取 excel 文件.如果有帮助.

This is pulling excel files off of an Intranet site. If that helps.

因为我确定有人会问，所以我只能使用 VBA.这将在工作场所使用，并且 90% 的计算机是共享的.我非常怀疑他们是否会为获得我的 Visual Studio 的业务费用而奔波.所以我必须用我所拥有的来工作.

Since I'm sure someone will ask, I can't use anything but VBA. This is going to be used at the workplace and 90% of the computers are shared. I highly doubt they'll spring for the business expense of getting me Visual Studio either. So I have to work with what I have.

任何帮助将不胜感激.

推荐答案

你可以在异步模式下使用 xmlhttp 和一个类来处理它的事件:

You can do this using xmlhttp in asynchronous mode and a class to handle its events:

http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/

那里的代码处理 responseText，但您可以调整它以使用 .responseBody.这是一个(同步)示例:

The code there is addressing responseText, but you can adjust that to use .responseBody. Here's a (synchronous) example:

Sub FetchFile(sURL As String, sPath)
 Dim oXHTTP As Object
 Dim oStream As Object


    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    Set oStream = CreateObject("ADODB.Stream")
    Application.StatusBar = "Fetching " & sURL & " as " & sPath
    oXHTTP.Open "GET", sURL, False
    oXHTTP.send
    With oStream
        .Type = 1 'adTypeBinary
        .Open
        .Write oXHTTP.responseBody
        .SaveToFile sPath, 2 'adSaveCreateOverWrite
        .Close
    End With
    Set oXHTTP = Nothing
    Set oStream = Nothing
    Application.StatusBar = False


End Sub

这篇关于从 VBA (Excel) 中异步下载文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！