Results 1 to 12 of 12
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from web (2000 rs 1)

    When I try to run a web query on the URL you provided, Excel (2002 SP-3) refuses to import any data. Do others experience the same?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from web (2000 rs 1)

    yes, I experience the same - "this query returned no data..." - same excel as you - 2002 sp3

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    import from web (2000 rs 1)

    http://www.capitalmarket.com/bulletin/bsebhav.asp?var=A

    i have this link site
    In this site are present many data in table-format.
    Each page is identify from a letter from A to Z
    I would want to import for each letter site the value present in the related page and put the data into a sheet...
    My idea .... but only this i am not an expert:
    Put into a sheet SERVICE into column A yhe letter from A to Z and cycling with a for next the url:

    http://www.capitalmarket.com/bulletin/bsebhav.asp?var= & letter present in column A of sheet SERVICE

    and put the data into sheet TABLE

    Can you help me?
    Please very important for me.
    TIA.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: import from web (2000 rs 1)

    Hans

    same answer as Brooke (XL 2002). I get this error message:
    Jerry

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from web (2000 rs 1)

    I got the alphabet.

    Excel 2000 SP3

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from web (2000 rs 1)

    The page Sal refers to contains many tables; you imported a table with navigation information, but none of the tables with data. It seems this web page doesn't want the data to be imported into Excel.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from web (2000 rs 1)

    Hans and other friends...
    Look here:
    Sub Importer_tableauPageWeb()
    'MichelXld le 09.06.2005
    '
    'activate Microsoft HTML Objects Library
    'activate Microsoft Internet Controls
    '
    Dim IE As InternetExplorer
    Dim maPageHtml As HTMLDocument
    Dim Htable As IHTMLElementCollection
    Dim maTable As IHTMLTable
    Dim j As Integer, i As Integer, x As Integer, Ligne As Integer
    Dim NbPages As Byte

    Application.ScreenUpdating = False

    For NbPages = 65 To 90
    Ligne = 0
    Sheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = Chr(NbPages)

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
    IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages)
    Do Until IE.readyState = READYSTATE_COMPLETE
    DoEvents
    Loop

    Set maPageHtml = IE.document
    Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table

    For x = 2 To Htable.Length - 1
    Ligne = Ligne + i + 1

    Set maTable = Htable(x)

    For i = 1 To maTable.Rows.Length 'each row in table

    For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row
    Cells(Ligne + i, j) = maTable.Rows(i - 1).Cells(j - 1).innerText
    Next j

    Next i
    Next x

    DoEvents
    Set IE = Nothing
    Next NbPages

    Application.ScreenUpdating = True
    End Sub

    Lie this code?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from web (2000 rs 1)

    Glad someone else was able to help you!

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from web (2000 rs 1)

    Ok, finaly i have a code...
    But not is wat i want;-(

    1) insert in only one sheet named TABELLA all record refered from each lettere of link.
    2) insert the records start from A2
    3) insert into A1 the name of first record, into B1 the name of the second records...
    4) not import a blank line, and insert up the record of the variuos letter link

    Ony this little modify;-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from web (2000 rs 1)

    I don't understand 3) insert into A1 the name of first record, into B1 the name of the second records...
    What do you mean by that?

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from web (2000 rs 1)

    ... sorry for english.

    The name of the records:

    COMPANY NAME (A1)
    OPEN (B1)
    ....

    Ops...
    Naturally not import the line of each page with: BSE INDEX (not is importat in my case)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from web (2000 rs 1)

    Sal,

    Here is a variation on Michel's code that imports everything into the TABLE worksheet. After the import, you must run another macro that removes the lines you don't want. We have given you lots of macros that do similar things in the past, you should be able to write this one yourself.

    Sub Importer_tableauPageWeb()
    'MichelXld le 09.06.2005
    'activate Microsoft HTML Objects Library
    'activate Microsoft Internet Controls

    Dim IE As InternetExplorer
    Dim maPageHtml As HTMLDocument
    Dim Htable As IHTMLElementCollection
    Dim maTable As IHTMLTable
    Dim j As Integer, i As Integer, x As Integer, Ligne As Integer
    Dim NbPages As Byte
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = Worksheets("TABLE")
    Ligne = ws.Range("A65536").End(xlUp).Row

    For NbPages = 65 To 90
    i = 1
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
    IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages)
    Do Until IE.readyState = READYSTATE_COMPLETE
    DoEvents
    Loop

    Set maPageHtml = IE.document
    Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table

    For x = 2 To Htable.Length - 1
    Ligne = Ligne + i + 1
    Set maTable = Htable(x)
    For i = 1 To maTable.Rows.Length 'each row in table
    For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row
    ws.Cells(Ligne + i, j) = maTable.Rows(i - 1).Cells(j - 1).innerText
    Next j
    Next i
    Next x

    DoEvents
    Set IE = Nothing
    Next NbPages

    Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •