Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-24-2014, 02:52 PM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default Extract data from web - Take a long time

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

Last edited by edneco; 06-25-2014 at 01:00 PM.
Reply With Quote
  #2  
Old 06-25-2014, 11:24 AM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default

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.......
Reply With Quote
  #3  
Old 06-25-2014, 12:26 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 06-25-2014, 12:28 PM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default

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

http://www.4shared.com/file/moNhVp6Y..._from_web.html
Reply With Quote
  #5  
Old 06-25-2014, 12:54 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 06-25-2014, 12:59 PM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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......
Reply With Quote
  #7  
Old 06-25-2014, 01:15 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Sorry still shows your file as "Blank". But I think you can now attach your file to this thread.
Try that.
Reply With Quote
  #8  
Old 06-25-2014, 01:20 PM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default

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

Extract from web.xlsm
Reply With Quote
  #9  
Old 06-25-2014, 03:34 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 06-26-2014, 12:25 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 06-27-2014, 12:20 PM
edneco edneco is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2007
Novice
Extract data from web - Take a long time
 
Join Date: Jun 2014
Posts: 12
edneco is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 06-28-2014, 05:54 PM
charlesdh charlesdh is offline Extract data from web - Take a long time Windows 7 32bit Extract data from web - Take a long time Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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 & ""
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract data from web - Take a long time 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
Extract data from web - Take a long time 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft