Results 1 to 6 of 6

Thread: Automation Help

  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Automation Help

    I am currently build an excel sheet that opens a webpage and copies information from it transfer into a database. It sometimes works but I sometimes have issues get one piece of information. The information is in a HTMLSelect box and I need the displayed text(selected/current) from the box. I get a RUN TIME error while doing this.
    Run-time error '-2147467259 (80004005)':
    Method 'Selected' of object 'IWHTMLSelect' failed
    and it only give me the option to END not Debug.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I am unable to help you but would like verry much to see your FULL code which you are using to extract info from an HTML page...

    TIA

  3. #3
    Join Date
    Jun 2004
    Posts
    6
    Quote Originally Posted by namliam
    I am unable to help you but would like verry much to see your FULL code which you are using to extract info from an HTML page...

    TIA
    Here it is. I am assume the webpage is open to start but you can use a SHELL function to open your browser via command line.

    Code:
    Sub transferMarketView()
    Dim tmpObj As Object
    Dim CurrentBook As Object
    Set CurrentBook = ActiveWorkbook
    AppActivate "MarketView", False
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    SendKeys "{TAB}"
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    SendKeys "{TAB}"
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    SendKeys "{DOWN}"
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 15
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    SendKeys "^a"
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    SendKeys "^c"
    AppActivate "Microsoft Excel - Importer", False
    Application.DisplayAlerts = False
    Workbooks(1).Sheets("Raw").Delete
    Workbooks.Add
    Set tmpObj = ActiveWorkbook
    Sheets.Add After:=Sheets(1)
    Sheets(2).Select
    Sheets(2).Name = "Raw"
    Sheets("Raw").Activate
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Sheets("Raw").Copy Before:=Workbooks("Importer.xls").Sheets(2)
    Dim tmpName As String
    tmpName = tmpObj.FullName
    Windows(tmpName).Activate
    ActiveWindow.Close
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    
    CurrentBook.Activate
    Control.Activate
    End Sub
    Once I finish it...I will add comments and repost. The VBA help is very useful especially once you know that it can be done and what commands to lookup. The function to look at are SHELL, APPACTIVATE, and SENDKEYS. And you can use these on any 32-bit application not just a web browser.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Why do you use this:

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime

    ??? And why dont you use the function dateadd???

    Regards

  5. #5
    Join Date
    Jun 2004
    Posts
    6
    Quote Originally Posted by namliam
    Why do you use this:

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime

    ??? And why dont you use the function dateadd???

    Regards
    I guess that would be little code; just never thought of it that way. Thanks for the tip.

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    also why use this ugly application.wait when you can use the nice and clean API call sleep

    Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

    Then just use
    Sleep 1000
    to make the code wait for 1 second....

    Regards

    P.S. Sorry i didnt think of this earlier....

Posting Permissions

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