Posts by smuzoen

smuzoen · Dec 14th 2018

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.

Code

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

smuzoen · Feb 26th 2016

Re: Extract URLs from website, name of attachment, and save attachment to a folder $8

I dont know if you are still looking for an answer however as previously stated the problem is (to quote your example) even though the link for your sample workbook has the narration Download Internet Files Automatically - User-Defined File Names - SAMPLE.xlsm the actual link is "attachment.php?attachmentid=68388&d=1456280246". As a result it is very difficult to parse the html in a dynamic website such as Ozgrid. In a static page you would have more luck as most likely the anchor links would refer to the true filename. In the case of (for example this Ozgrid page) you would have to use the HTML DOM and use node attributes to get the file name which is the inner text of the node. As John_w said to create a general solution would be extremely difficult as there is so many ways to write a webpage. If you had a set of web pages and by some fluke the html code is similar then you could write a solution - even regular expressions would be an option (albiet in my opinion not the best option).
If you were to inspect the source for the pages your want to parse to get filenames and find they are similar then perhaps you could get a solution. Not sure how far you want to pursue it though. It is an interesting problem in that it is a challenge. I think the best way myself is to use the DOM, get all the nodes by tag (anchor) and hope the inner text contains the filename proper. It is easy to modify your sample workbook to do the latter - however whether this would work on the pages you are interested in cannot be know unless you allowed the developer to take a look at the source.
One final option is to take the program John_w provided a link for and then parse the downloaded directories from your hard drive for the file extensions of interest and read it back into an Excel workbook......

smuzoen · Jul 8th 2014