Stream.SaveToFile 上的 VBA 错误

Question

我正在尝试从另一篇文章 (26486871) 修改 VBA 脚本。

该脚本将下载一个 Zip 文件，提取一个文本文件并将数据导入 Excel。

我不知道 VBA，所以我将一次处理每个函数。

创建一个具有随机名称的临时目录..........................完成

从公共(public)服务器下载 Zip 文件..........................错误

提取文本文件(20MB，制表符分隔).......................... 还没有

将数据导入打开的工作表(覆盖现有数据)...尚未

我收到 Stream.SaveToFile 的运行时错误:
“参数类型错误、超出可接受范围或存在冲突。”

我尝试了选项 1、2 和 adSaveCreateNotExists。但是，Zip 文件不会保存到临时文件夹。

我很感激任何帮助。

'Main Procedure
Sub DownloadExtractAndImport()

Dim url As String
Dim targetFolder As String, targetFileZip As String, targetFileTXT As String

Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
Dim newSheet As Worksheet

url = "http://www.example.com/data.zip"
targetFolder = Environ("TEMP") & "\" & RandomString(6) & "\"
MkDir targetFolder
targetFileZip = targetFolder & "data.zip"
targetFileTXT = targetFolder & "data.txt"

'1 download file
DownloadFile url, targetFileZip

End Sub

Private Sub DownloadFile(myURL As String, target As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Msxml2.ServerXMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile targetFile, 1  ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Sub

Private Function RandomString(cb As Integer) As String

Randomize
Dim rgch As String
rgch = "abcdefghijklmnopqrstuvwxyz"
rgch = rgch & UCase(rgch) & "0123456789"

Dim i As Long
For i = 1 To cb
    RandomString = RandomString & Mid$(rgch, Int(Rnd() * Len(rgch) + 1), 1)
Next

End Function

Answer 1

这只是一个错字:它必须是 target，而不是 targetFile，因为未指定 targetFile。请使用 Option Explicit 来防止这样的错误。