Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    webpage info into Excel.... (2000 sr 1)

    this is the big code of Hans.
    Option Explicit
    Sub PostalCodes()
    Dim ie As Object
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Set ie = CreateObject("InternetExplorer.Application")
    On Error GoTo errHandler
    lngMaxRow = Range("A65536").End(xlUp).Row
    With ie
    For lngRow = 2 To lngMaxRow
    .navigate "http://www.canadapost.ca/personal/tools/pcl/bin/quick-e.asp"
    Do While .busy
    DoEvents
    Loop
    Do While .ReadyState <> 4
    DoEvents
    Loop
    With .document.Forms(1)
    'Street Number
    .street_number.Value = Range("A" & lngRow)
    'Street Name
    .street_name.Value = Range("B" & lngRow)
    'City
    .city.Value = Range("C" & lngRow)
    'AB = Alberta, BC = British Columbia, MB = Manitoba, NB = New Brunswick, _
    NL = Newfoundland And Labrador, NS = Nova Scotia, NT = Northwest Territories, _
    NU = Nunavut, ON = Ontario, PE = Prince Edward Island, QC = Quebec, _
    SK = Saskatchewan, YT = Yukon
    .prov.Value = Range("D" & lngRow)
    .submit
    End With
    Do While Not CBool(InStrB(1, .document.URL, "cp_search_response-e.asp"))
    DoEvents
    Loop
    Do While .busy
    DoEvents
    Loop
    Do While .ReadyState <> 4
    DoEvents
    Loop
    On Error Resume Next
    'CONSIDERARE PRIMA CELLA COME POSISZIONE "ZERO"
    Range("E" & lngRow) = UCase(.document.all.tags("table").Item(11).Rows(1) .Cells(7).innerText)
    Range("F" & lngRow) = UCase(.document.all.tags("table").Item(11).Rows(1) .Cells(1).innerText)

    On Error GoTo errHandler
    Next lngRow
    End With

    errHandler:
    ie.Quit
    Set ie = Nothing
    Exit Sub
    End Sub

    is posible to arrange and get value from http://www.bancaroma.it/abicab/index.asp
    see attached file

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

    Re: webpage info into Excel.... (2000 sr 1)

    Sal, the idea behind this kind of code is to look at the HTML source of the web pages and to determine the name of the input fields, and the number of the table, row and column of the output cells. Everything starts counting at 0.

    For example, in the result page, the return values are in the second table, so you use

    .document.all.tags("table").Item(1)

    Remember, the first table is Item(0), the second one is Item(1).

    The name of the Banca is in the first row, fourth cell, so you use

    .Rows(0).Cells(3).innerText

    etcetera. See attached code.

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

    Re: webpage info into Excel.... (2000 sr 1)

    GOOD CODE, GOOD EXPLAIN! (naturally no WORDS)
    Tks.
    but to make visible FALSE or TRU Intrernet Explorer during the macro work i save time to rol the web page or is equal?

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

    Re: webpage info into Excel.... (2000 sr 1)

    When I run the code, Internet Explorer remains invisible. I think the code would be slower if Internet Explorer was made visible.

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

    Re: webpage info into Excel.... (2000 sr 1)

    hummmmmm... This web archive is updating at every week, and assuming i have this result after the first search mcaro. (see attach)
    Naturally on the value aldeardy geted not repeat the search macro....
    How i can make this?

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

    Re: webpage info into Excel.... (2000 sr 1)

    You could test within the loop (For lngRow = 2 To lngMaxRow ... Next lngRow) whether the cell in column C in the current row is empty.

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

    Re: webpage info into Excel.... (2000 sr 1)

    My Teacher see wath your student have make, and tell me!

    in the tag: 'MY MODIFY

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

    Re: webpage info into Excel.... (2000 sr 1)

    That looks good. The essential part is the test on column C:

    If wksList.Cells(lngRow, 3).Value = "" Then
    ...
    End If

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

    Re: webpage info into Excel.... (2000 sr 1)

    Tks, for your comment.....
    After around 1 year on this forum is a minimal i have make a line of code my self...;-)

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

    Re: webpage info into Excel.... (2000 sr 1)

    From small beginnings come great things. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: webpage info into Excel.... (2000 sr 1)

    ... Tks.

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

    Re: webpage info into Excel.... (2000 sr 1)

    oh, oh,....
    My Teacher...
    Casually i have see in my cnonology of visit site...
    Is present for each page visited from the macro to see into web page a link....
    My actually macro search for 9118 record... in this case i have 9118 link into cromnology of visit web...
    Exis a command to not make the link into cronology?
    I have attach a imge to show a little part of link....

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

    Re: webpage info into Excel.... (2000 sr 1)

    Change the line

    .navigate "http://www.bancaroma.it/abicab/index.asp"

    to

    .navigate "http://www.bancaroma.it/abicab/index.asp", 2

    The 2 corresponds to navNoHistory: "Do not add the resource or file to the history list."

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

    Re: webpage info into Excel.... (2000 sr 1)

    Hummmm,
    i have add the numer 2 into a string param but the macro add every link always into History link/cronology......

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

    Re: webpage info into Excel.... (2000 sr 1)

    What do you mean by "add the numer 2 into a string param"? It shouldn't be a string.

Page 1 of 3 123 LastLast

Posting Permissions

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