CSV string from GET request incomplete when storing in variable

Question

In VBA, I currently have code that returns a CSV string from the internet. However, it seems like the entire file is not being captured when I try to store it in a string variable using .responseText

I've heard string sizes are limited, which makes this problem understandable (I'm trying to store 1000 rows of data into a variable - which is probably not possible). I was wondering if there is a way to download the CSV file straight from the source given that I have the URL to get the response? Here is the relevant code that I have:

              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

For reference, I have looked at the following links to try to solve this issue:

Arguments out of acceptable range

Downloading file from web with VBA

HTTP response text returning incomplete Data

*I've heard string sizes are limited* - In VBA the limit is ~2GB so that's not likely going to be the issue, similarly there is no specified limit on .responseText — Alex K., Jul 26 '18 at 13:19
Would it be possible that the website I am using would only return part of the response @Alex K. ? — Jack Moody, Jul 26 '18 at 13:21
I've seen websites download more of the page as you scroll down.. maybe this is (not) happening here. — CLR, Jul 26 '18 at 13:24
@Jeeped would something like the post from donato in [this post](http://www.vbaexpress.com/forum/showthread.php?33145-Download-from-Excel-via-URLDownloadToFile) work like you are saying with `urldownloadtofile`? — Jack Moody, Jul 26 '18 at 13:31
urldownloadtofile cannot be used with authentication header afaik — Alex K., Jul 26 '18 at 13:35
If you install Fiddler & run the request you will be able to see what the server is responding with in a raw form to see if its what you expect. — Alex K., Jul 26 '18 at 13:35
The URL I am requesting responds by downloading a CSV file if used from a normal browser. Is there a way to just automate the process of downloading from the browser and then download the file as a certain name like "Jira1.csv"? — Jack Moody, Jul 26 '18 at 13:49

score 0 · Accepted Answer · answered Jul 30 '18 at 15:52

My code ended up looking like this:

    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

I had to change from .responseText to .responseBody.

CSV string from GET request incomplete when storing in variable

1 Answers1