Extract data from web

edneco · #1 06-24-2014, 02:52 PM

Hello everyone.... I have this file that extracts some data from web, but the extracting take a long time to finish, about 5 minutes!!!!!!

This is a sheet that´s gonna take some football data (tables, journeys).....

Can you see the code and help me how can I reduce the time......

http://www.4shared.com/file/moNhVp6Y..._from_web.html

edneco · #2 06-25-2014, 11:24 AM

Hello again.... I have an idea to solve this..... The sheet is gonna take a lot of tables, I only need one table....

But in the next week, I´m going to need another table and it´s not fun to change the code, by hand, all weeks.

The first week I need this link:
(http://www.zerozero.pt/edition.php?i...1&jornada_in=1)

By the second week the link is the same, except the last number (1), This need to be change to 2.

If you can see in the cell "I4", I can select the number I want. So I need that the code read that number (cell) and change it.

I think it´s easy, but my knowlegde about VBA is very limited...... Need some help from you.....

Thank you in advance for any help you can give.......

charlesdh · #3 06-25-2014, 12:26 PM

HI,

The link you provided for your code does not have your workbook on it.
If you can repost it so that we can see what you have.

edneco · #4 06-25-2014, 12:28 PM

Sorry... This one is OK.....

http://www.4shared.com/file/moNhVp6Y..._from_web.html

charlesdh · #5 06-25-2014, 12:54 PM

Sorry I must be doing something wrong. But I still can not get the file.
When I click Download it say file is ready but to no avail No file.

edneco · #6 06-25-2014, 12:59 PM

Quote:

Originally Posted by charlesdh

Sorry I must be doing something wrong. But I still can not get the file.
When I click Download it say file is ready but to no avail No file.

And this??? http://www.4shared.com/file/moNhVp6Y..._from_web.html

I don´t know if you need to have an account on 4shared or not.... Not sure about that.... But try this link now......

charlesdh · #7 06-25-2014, 01:15 PM

Sorry still shows your file as "Blank". But I think you can now attach your file to this thread.
Try that.

edneco · #8 06-25-2014, 01:20 PM

Let´s find out if now it´s ok.....

Extract from web.xlsm

charlesdh · #9 06-25-2014, 03:34 PM

Thanks. I'll take a look. But can not promise any thing. Have go for now. Will try to look at this tomorrow.
Perhaps another member will also pick up on this.

charlesdh · #10 06-26-2014, 12:25 PM

edneco,

I looked at your file. But, sad to say I will not be able to help.
It runs ok until it get to this line.

Code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.zerozero.pt/edition.php?id_edicao=69891&fase=70218&jornada_in=1", Destination:=Range( _
        "$DO$13"))
        .Name = "edition.php?id_edicao=69891&fase=70218&jornada_in=1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "20"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With

This line runs a long time.
It then goes to the next code and too it take a long time.
I'm not that versed working with the web.
Hopefully another member can pick up on this.

edneco · #11 06-27-2014, 12:20 PM

Hello... I have this file now and I think I´m in the right way, but there´s something that doesn´t work......

Code:

Private Sub Update_Click()
Dim Valor

Valor = Range("I4").Value

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.zerozero.pt/edition.php?id_edicao=69891&fase=70218&jornada_in=valor", Destination:=Range("$DO$13"))
        .Name = "edition.php?id_edicao=69891&fase=70218&jornada_in=valor"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "21"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With

I need that the word "valor" assume the value on the cell "I4", when I change it.

When I put the value "1" on the cell "I4", the link should be like this:

(http:........zerozero.pt/edition.php?id_edicao=69891&fase=70218&jornada_in= 1)

And When I put the value "10" on the cell "I4", the link should be like this:

(http:........zerozero.pt/edition.php?id_edicao=69891&fase=70218&jornada_in= 10)

I don´t know if you can help me on this, but I guess that something is missing.... Or is just impossible to do this???

Extract from web_1.xlsm

charlesdh · #12 06-28-2014, 05:54 PM

See if this works.

Code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.zerozero.pt/edition.php?id_edicao=69891&fase=70218&jornada_in=" & Valor & "", Destination:=Range( _
        "$DO$13"))
        .Name = "edition.php?id_edicao=69891&fase=70218&jornada_in=" & Valor & ""

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Extract key data from word	iliauk	Word	3	11-08-2013 04:37 PM
Outlook taking a very long time to open emails that feature a background picture	Mr Davo	Outlook	0	07-03-2012 12:36 AM
formula taking long time to distinct word	gsrikanth	Excel	2	03-18-2012 03:30 AM
Adding a long column of time increments in seconds	klippert	Excel	1	09-18-2010 12:26 AM
How long does MS Outlook Calendar retain past data?	dillon	Outlook	1	08-09-2009 09:24 AM