代码之家 › 专栏 › 技术社区 › Jack Moody A.J. Uppal

当存储在变量中时,来自GET请求的CSV字符串不完整

get csv excel-vba vba

Jack Moody A.J. Uppal · 技术社区 · 8 年前

在VBA中,我目前有一个从internet返回CSV字符串的代码。但是,当我试图使用 .responseText

我听说字符串的大小是有限的,这使得这个问题可以理解(我试图将1000行数据存储到一个变量中——这可能是不可能的)我想知道是否有一种方法可以直接从源下载CSV文件,因为我有URL来获取响应?以下是我的相关代码:

              Dim JiraReq As New MSXML2.XMLHTTP60    
              With JiraReq
                  ' Create Session
                 downloadUrl = https://myurlishere.atlassian.net 
                 ' This is not the actual form of the URL, but it does 
                  ' successfully grab the CSV string (but the string variable 
                  ' will not store all of it - probably because of the size)
                 .Open "GET", downloadUrl, False
                 .setRequestHeader "Authorization", "Basic " + EncodeBase64(username + ":" + password)
                 .setRequestHeader "Cache-Control", "no-cache"
                 .setRequestHeader "Pragma", "no-cache"
                 .send
               End With

                CSVData = JiraReq.responseText
                If JiraReq.Status = 200 Then
                    Set oStream = CreateObject("ADODB.Stream")
                    oStream.Open
                    oStream.Type = 1
                    oStream.Write JiraReq.responseText
                    Filename = "C:\file" & i & ".csv"
                    oStream.SaveToFile Filename, 2
                    oStream.Close
                End If

作为参考,我查看了以下链接以尝试解决此问题:

Arguments out of acceptable range

Downloading file from web with VBA

HTTP response text returning incomplete Data

1 回复 | 直到 8 年前

Jack Moody A.J. Uppal 8 年前

我的代码最后看起来是这样的:

    For i = 1 To pageCount Step 1
        ' MsgBox "Entering For Loop"
        Dim startNumber As Integer
        startNumber = (i - 1) * 1000

        Dim downloadUrl As String
        downloadUrl = "https://myurl.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/" & CStr(filterID) & "/SearchRequest-" & CStr(filterID) & ".csv?tempMax=1000&pager/start=" & CStr(startNumber)

        With JiraReq
          ' Create Session
         Debug.Print "Getting page " & CStr(i) & " of " & CStr(pageCount)
         .Open "GET", downloadUrl, False
         .setRequestHeader "Authorization", "Basic " + EncodeBase64(username + ":" + password)
         .setRequestHeader "Cache-Control", "no-cache"
         .setRequestHeader "Pragma", "no-cache"
         .send
       End With

       file_name = "C:\VBA\file" & i & ".csv"
       If JiraReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write JiraReq.responseBody
            oStream.SaveToFile file_name, 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
       End If

我不得不改变 .responseText 到 .responseBody .