Hello,
Is it possible to read a file on the Internet without Selenium?
Only using commands that VBA already has (such as Open, for example), without having to install add-ons etc...
Thanks!
Hello,
Is it possible to read a file on the Internet without Selenium?
Only using commands that VBA already has (such as Open, for example), without having to install add-ons etc...
Thanks!
Wilker,
Check into XMLHttpRequest. You can request a webpage and download a file or simply read it from VBA. The below code is untested because I am on Linux but give it a shot it should point you in the right direction.
Option Explicit
Sub Read_file ()
Dim http as Object
Dim oStream as Object
Dim url as String
set http = CreateObject("Microsoft.XMLHTTP")
url = "https://www.w3.org/TR/PNG\iso_8859-1.txt" 'just a random internet file I found for trying it out
http.Open "GET", url, False
html.Send
if http.Status = 200 then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.WriteText http.responseBody
oStream.SaveToFile "C:\savefile.txt", 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
end Sub
Display More
Here is an alternative to XMLHTTP using ServerXMLHTTP. XMLHTTP is designed for client applications built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications (HTTP client stack, WinHTTP). As a general statement ServerXMLHTTP is quite robust and is server safe. ServerXMLHTTP is recommended for server applications and XMLHTTP is recommended for client applications and both have their advantages depending on the needs of the application.
Option Explicit
Sub ReadFile()
Dim oHttp As Object
Dim urlFile As String, readURLFile As String
Dim strPath As String
strPath = "c:\temp\test.txt"
On Error GoTo Error_Handler
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
urlFile = "https://www.w3.org/TR/PNG/iso_8859-1.txt"
Call oHttp.Open("GET", urlFile, False)
Call oHttp.Send
'Check for any errors reported by the server
'Only basic error handling offered here - depending on
'how robust the application will determinethe level of
'error handling
If oHttp.Status >= 400 And oHttp.Status <= 599 Then
readURLFile = ""
Else
'if no errors readURLFile contains the text file from
'source web page
readURLFile = oHttp.ResponseText
Call WriteDataToTextFile(strPath, readURLFile)
End If
MsgBox "Data written to file", vbOKOnly
Error_Handler:
If oHttp.Status >= 400 And oHttp.Status <= 599 Then
MsgBox "The following error has occured." & vbCrLf & _
"Error Number: " & oHttp.Status & vbCrLf & _
"Error Source: readURLFile" & vbCrLf & _
"Error Description: " & oHttp.StatusText, _
vbCritical, "An Error has Occured!"
End If
End Sub
Sub WriteDataToTextFile(ByVal strPath As String, ByVal data As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile(strPath)
oFile.Write (data)
oFile.Close
Set fso = Nothing
Set oFile = Nothing
End Sub
Display More
As in the commented code I would recommend to have some more specific error handling - if there is a problem then more direct error handling will help you narrow down the cause and will make the application more user friendly. If the specific error is of no concern to you simply use a catch all. I would always recommend error handling with this type of application. As you would appreciate a lot of unforeseen errors may occur when reading data over the internet. The second sub is simply to write the data scraped to a text file - modify the destination to your needs.
If you are interested in the difference between XMLHTTP and ServerXMLHTTP then there is a good discussion here -> https://support.microsoft.com/en-au/...-serverxmlhttp
Ok, thank you!
Don’t have an account yet? Register yourself now and be a part of our community!