How to get a string or a numeric data element from the internet and save it to a variable using Access VBA

Question

Hi,

I used the following subroutine to get a file from the internet and save it to a file, using Access VBA:

Sub DownloadFile()

Dim url As String, filePath As String, attempts As Integer
Dim WinHttpReq As Object, oStream  As Object
url = "http://URL/SIS.txt?dt=" & Format(Now(), "yyyymmddhhmmss")
filePath = "c:\sos\SIS.txt"
attempts = 3
Set WinHttpReq = CreateObject("MSXML2.XMLHTTP.6.0")
On Error GoTo TryAgain

TryAgain:
attempts = attempts - 1
Err.Clear
If attempts > 0 Then
WinHttpReq.Open "GET", url, False
' WinHttpReq.setrequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
WinHttpReq.Send
url = WinHttpReq.ResponseBody

   If WinHttpReq.Status = 200 Then
      Set oStream = CreateObject("ADODB.Stream")
      oStream.Open
      oStream.Type = 1
      oStream.Write WinHttpReq.ResponseBody
      oStream.SaveToFile filePath, 2 ' 1 = no overwrite, 2 = overwrite
      oStream.Close
   End If
Else
   MsgBox ("A4. No internet connection is available.")
End If

End Sub

Now, instead of getting a file, I need to change the subroutine to get a string or a numeric data element and save it to a variable, Any help?

Regards.

Answer 1

How is the string or numeric data set up? Is it returned by the website/server you're querying? If so, it might be as simple as storing the returned .responseText into a variable.

Answer 2

The string data is some alpha numeric characters variable in length whereas the numeric data is either integer, single, or double. The data might also come as Date and Boolean. Please write the syntax for code line 4-6 for each data type.

Answer 3

Unfortunately, if it were me, I wouldn't be using stream object, because we're not dealing with a file data here. Like I said earlier, if the web server simply sends the data as text or number, then you should be able to simply assign the contents of the responseText property into a variable. Have you tried that?

Answer 4

Please advise what values ostream.type (code line 4) takes other than one? Also advise whether ADO stream would be better to use than ostream. ADO stream looks like it handles file, record, and field and both binary and text data. I'll try after I decide which way to go.

Answer 5

Need help.

I'm completely lost. I need a tutorial with examples on how to use ADO stream in VBA to web-extract a field from a record of a database table, say a web-API Access database. If there is no such a tutorial, please guide me to a sample subroutine somewhere on the internet doing that.