Import a table from a webpage into excel fast

Question

I am trying to implement a code where I can import multiple tables from a website. I am able to do so using the following code. However, the code is too slow.

Sub FetchData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;%url%", Destination:=Range( _
        "$A$1"))
        .Name = "xyz"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Can someone suggest a code that will give the output fast?

What is "slow"? 10 seconds? A minute? Retrieving the web page is likely to be the bottleneck. — JJJ, Aug 04 '13 at 10:32
Is something preventing you from creating the web query 'by hand' and just refreshing it in VBA ? — iDevlop, Aug 04 '13 at 11:41
It is taking 4 mins. It is working for me. This is not the issue. But I would like it if some code makes it a little faster to import the table from the web. — user2650238, Aug 05 '13 at 10:57
See this link http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards — Siddharth Rout, Aug 06 '13 at 13:35

score 0 · Answer 1 · answered Aug 16 '13 at 23:31

A couple of ideas come to mind, have you turned screen updating to false, this can really speed up the code if a lot is going on on the screen whilst the code is running.

Application.ScreenUpdating = False

The second idea is that you have a lot of '.l operators in your code, have you checked that you need to set them all or can you delete some of them. I've heard that removing un-necesary '.' operators can speed up code considerably.

Import a table from a webpage into excel fast

1 Answers1