Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Request for code critique (VBA/Excel/2003 SP3)

    The following code was originally running without lines 40 through 80 and frequently threw an unknown error at line 30. Moments later the code would continue successfully when run in the Debug mode. Lines 40 through 80 were added and appear to have resolved the problem however I am concerned that I may be approaching the problem from the wrong direction. Any comments will be most welcome.

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black>
    <font color=blue>Function</font color=blue> GetQueryData(strStartURL <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>, Abort <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>, _
    <font color=blue>Optional</font color=blue> strEndPage <font color=blue>As</font color=blue> <font color=blue>String</font color=blue> = vbNull<font color=blue>String</font color=blue>) <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=448800>' Jefferson F. Scher 05/04/2006</font color=448800>
    <font color=448800>' Uses IE DOM to open web page and retrieve the contents _
    of one HTML table cell into a string</font color=448800>
    <font color=448800>' Requires adding the following under Tools>References:</font color=448800>
    <font color=448800>' Microsoft HTML Object Library</font color=448800>
    <font color=448800>' Microsoft Internet Controls</font color=448800>

    <font color=blue>Dim</font color=blue> PostURL <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> MainPost <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> Board <font color=blue>As</font color=blue> String
    <font color=blue>Dim</font color=blue> ArrCol <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> ArrRow <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>

    10 GetQueryData = "Complete"

    <font color=448800>'Create browser object references and open an IE window</font color=448800>
    <font color=blue>Dim</font color=blue> ieNew <font color=blue>As</font color=blue> New InternetExplorer

    LoadPage:
    20 <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>Resume</font color=blue> <font color=blue>Next</font color=blue>
    30 <font color=blue>With</font color=blue> ieNew
    40 <font color=blue>If</font color=blue> Err <font color=blue>Then</font color=blue>
    50 <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>GoTo</font color=blue> 0
    60 Sleep 500
    70 <font color=blue>GoTo</font color=blue> LoadPage
    80 <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    90 .Visible = <font color=blue>True</font color=blue> <font color=448800>'show window</font color=448800>
    100 .navigate strStartURL 'open page
    110 <font color=blue>While</font color=blue> <font color=blue>Not</font color=blue> .readyState = READYSTATE_COMPLETE
    120 Sleep 500 <font color=448800>'wait 1/2 sec before trying again</font color=448800>
    130 <font color=blue>Wend</font color=blue>

    140 <font color=blue>If</font color=blue> strEndPage <> vbNull<font color=blue>String</font color=blue> <font color=blue>Then</font color=blue> <font color=448800>'check for ending page</font color=448800>
    <font color=blue>Dim</font color=blue> intQueryPos <font color=blue>As</font color=blue> <font color=blue>Integer</font color=blue>, strCurrentPage <font color=blue>As</font color=blue> String
    150 <font color=blue>Do</font color=blue>
    160 intQueryPos = InStr(1, .LocationURL, "?")
    170 <font color=blue>If</font color=blue> intQueryPos > 0 <font color=blue>Then</font color=blue>
    180 strCurrentPage = Left(.LocationURL, intQueryPos - 1)
    190 <font color=blue>If</font color=blue> <font color=blue>StrComp</font color=blue>(strCurrentPage, strEndPage, vbTextCompare) = 0 <font color=blue>Then</font color=blue> <font color=blue>Exit</font color=blue> <font color=blue>Do</font color=blue>
    200 <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    210 Sleep 500 <font color=448800>'wait 1/2 sec before trying again</font color=448800>
    220 <font color=blue>Loop</font color=blue>
    <font color=448800>' Now wait for page to finish loading</font color=448800>
    230 <font color=blue>While</font color=blue> <font color=blue>Not</font color=blue> .readyState = READYSTATE_COMPLETE
    240 Sleep 500 <font color=448800>'wait 1/2 sec before trying again</font color=448800>
    250 <font color=blue>Wend</font color=blue>
    260 <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    270 End <font color=blue>With</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Request for code critique (VBA/Excel/2003 SP3)

    I don't think it's possible to have an error on "With ieNew". What code was on line 30 before?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Request for code critique (VBA/Excel/2003 SP3)

    I would suggest replacing:
    <code> Dim ieNew As New InternetExplorer</code>
    with:
    <code>
    Dim ieNew As InternetExplorer
    Set ieNew = New InternetExplorer</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Request for code critique (VBA/Excel/2003 SP3)

    Don

    This article may be of some help: VB6: Taming the Wild New IE Object.

    Alan

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Request for code critique (VBA/Excel/2003 SP3)

    Thank you Alan
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Request for code critique (VBA/Excel/2003 SP3)

    Hi Jefferson
    <hr>I don't think it's possible to have an error on "With ieNew"<hr>
    I wouldn't have thought so either, but that was the highlighted line on Debug.

    Line 30 was unchanged except for the addition of numbering from the source which I picked up <!post=here,747160>here<!/post>.
    Regards
    Don

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Request for code critique (VBA/Excel/2003 SP3)

    If the code compiles correctly, then you must have added a reference to Microsoft Internet Controls (Tools>References). Next time you debug, can you open the locals window and see whether ieNew was created?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Request for code critique (VBA/Excel/2003 SP3)

    What an imperfect world we live in! Using the original code; everything works again as it should. Thanks to all for the interest.
    Regards
    Don

Posting Permissions

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