Optimize HTML parsing of several links

matcunni

New Member
Joined
Feb 6, 2016
Messages
9
Hi all! I'm working in a piece of code which takes a directory's URL from a range of cells, and overwrites each cell values with the URL of an image that's inside each directory.

Something like this:

(Cell contents before macro) --> (Cell contents after macro)

Range C1:C5
http://server.com/dir01/2018-04-07/ --> http://server.com/dir01/2018-04-07/Image15.jpg
http://server.com/dir02/2018-04-07/ --> http://server.com/dir02/2018-04-07/Image32.jpg

Range D1:D5
http://server.com/dir01/2018-04-08/ --> http://server.com/dir01/2018-04-08/Image12.jpg
http://server.com/dir02/2018-04-08/ --> http://server.com/dir02/2018-04-08/Image13.jpg
.
.

I'm pretty new to this and the CODE I'm using is working, but it's VERY slow.. I've 135 Directory URLs in each column

Code:
Public Enum READYSTATE
    READYSTATE_UNINITIALIZED = 0
    READYSTATE_LOADING = 1
    READYSTATE_LOADED = 2
    READYSTATE_INTERACTIVE = 3
    READYSTATE_COMPLETE = 4
End Enum

Sub URLPopulator()

    Dim xRg As Range
    Dim xCol As Long
    Dim xURL As String
    Dim counter1 As Integer
      
    On Error Resume Next
    Application.ScreenUpdating = False
    Set Rng = ActiveSheet.Range("C1:C5,D1:D5")  'This are the cells with the urls of the folders I'm accesing. 
   
    For Each cell In Rng
        counter1 = counter1 + 1

            If counter1 = 12 Then
                Kill_IE                       'I call the Sub to kill iexplorer.exe process when there are 12 iexplorer.exe instances loaded on memory. I then reset the counter.
                counter1 = 0
            End If

        xURL = ImportURL(cell)  'Call the function which overwrites the cell contents
 
        cell.Value = cell.Value + xURL
        xCol = cell.Column
        Set xRg = Cells(cell.Row, xCol)

    Next

        Application.ScreenUpdating = True

End Sub

This is the code of the Iexplorer.exe killer
Code:
Sub Kill_IE()

    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run "taskkill /F /IM iexplore.exe", windowStyle, waitOnReturn

End Sub

And this is the slow function:
Code:
Function ImportURL(url1) As String
    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    
    Set ie = New InternetExplorer
    
    ie.Visible = False
    ie.navigate url1
    
    Do While ie.READYSTATE <> 4
        DoEvents
    Loop
    
    Set html = ie.document
    Dim Matu, Seba, Pepe
    
    Matu = html.DocumentElement.innerHTML
    Seba = InStr(720, Matu, "jpg") + 5
    Pepe = Mid(Matu, Seba, 17) 'After this piece of code, Pepe = ImageXX.jpg , I couldn't find another way to reach the name of the jpg other than this, beacuse it's inside a duplicate tag "< a>"

    ImportURL = Pepe
    Set ie = Nothing

End Function

Any help is really appreciated
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top