Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts

    SendKeys With A URL in VBA

    Hi

    I've written some code from Excel using SendKeys to send a URL to a browser and then enter some search terms in relevant boxes. It works 95% of the time and is very happy. However should there be a delay in the webpage opening up, then it's still putting the searchterms in the URL and giving me an error message.
    Is there a way of sending a URL and then not continuing the code until that page has fully loaded? I've tried a few seconds delay. But is there something I could do to send something back to the Excel sheet that will trigger off the rest of the Macro (or a new subroutine).

    The bit of code I have for this bit is...

    Code:
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys ("http://MYURL.aspx"), True
    Application.Wait (Now + TimeValue("0:00:01"))
    
    SendKeys ("{ENTER}"), True
    Application.Wait (Now + TimeValue("0:00:04"))
    Other than a longer wait time, how can I improve this bit?

    Thanks

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,662
    Thanks
    41
    Thanked 189 Times in 188 Posts
    I have written some pages that select "other pages" to be viewed in an iframe- doing this calles my DB and then posts the other page so my page always loads after my display page does... maybe this is more clear...
    *note pseudo code*
    Code:
    page "Demo"
    selection box 
      item A
      item B
      item C
    <div>
    <iframe> source of iframe is page "LoadMe"</iframe>
    </div>
    ...........................
    onSelectionsBox_Change(send View <- object that changed to)
    LoadMe?View
    ...........................
    page LoadMe
    onPageLoad{
    inner.html = call database and select object "View" (sent from other page call)
    }
    the code is more complex obviously- but the logic is there.... by calling the other page you now have to wait for it to finish it's postback before you can postback therefore your page always loads after that page is done loading- does that help?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • Users who have thanked alykins for this post:

    sonofjack (02-02-2012)

  • #3
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Thanks for the reply.
    I'm relatively new to VB programming so I'm still learning the functions and methods. So various bits of code I see on forums aren't immediately recognisable.
    But I see the logic there and will explore and experiment further

    Many thanks

  • #4
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Well I think I'm getting there but I'm stumbling a bit now. Can anyone help me please?

    I've tried doing it this way which works to a certain point.

    Code:
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer   
        ie.Navigate "http:myurl"
        
        'Loop until ie page is fully loaded
        Do Until ie.ReadyState = READYSTATE_COMPLETE
        Loop
        
        ie.Visible = True
    And I can populate the form fields with

    Code:
    ie.Document.getElementById("field1").Value = "SOME DATA"
    ie.Document.getElementById("field2").Value = "SOME MORE DATA"
    However, my stumbling blocks are...

    I want it to put the URL into the existing Activated page that I previously used by AppActivate
    So can I replace some of the above code so it sends the URL to a page with the title of .... 'specify title here'?


    Also, this bit doesn't seem to work
    Code:
    ie.Document.getElementById("searchbutton").Click
    The form button is actually
    <input type="submit" value="Search" class="searchbutton">
    and therefore doesn't have an ID. But what is the equivalent of pressing the Submit button that I need for my code?


    Many thanks

  • #5
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,662
    Thanks
    41
    Thanked 189 Times in 188 Posts
    I am getting confused a little- are you doing explicit VBa? ie vb windows form? what framework are you running under? I don't see how a VB app will have access to a non vb.net webapp ... there might be a way but I have never thought to try it since typically vb.net web apps are separate from vb win apps- I can think on it more though...
    *I could be mistaken here- my vb xp is mainly in working with legacy VBA and vb.net webapps... all of my new development is in C# so my quote of typically vb.net apps and vba's are separate may be flawed- but I am pretty sure it is accurate

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • Users who have thanked alykins for this post:

    sonofjack (02-02-2012)

  • #6
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Hi

    I'm currently working in Excel using the VB editor to build macros.
    My latest tweak does open the URL in a new IE window and populates the fields as I like but I can't quite find out how to combine it with the AppActivate method of finding an already open window, nor have it click the Submit button.

    If I can do that bit then I'm well on my way

    Cheers

  • #7
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Nearly there....
    I've sorted out the Submit button part. I'll post it here in case anyone else is looking for the same thing.
    Name of the Form is searchform
    So I'd tried playing around with different variations and finally got this to work..

    Code:
    ie.Document.searchform.Submit
    But I'm still stuck with my code opening up a new Internet Explorer instead of it activating the same one.

    Can anyone help me tweak my code with that please?


    Code:
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer   
        ie.Navigate "http:myurl"
        
        'Loop until ie page is fully loaded
        Do Until ie.ReadyState = READYSTATE_COMPLETE
        Loop
        
        ie.Visible = True
    I know the Set ie = New InternetExplorer bit opens a new window.
    But how do I change this to do the same thing as AppActivate("Title")

    Many thanks

  • #8
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,662
    Thanks
    41
    Thanked 189 Times in 188 Posts
    this might help you, parts of the process class
    get array of processes
    then search through them for ie (array of process[] 'es- in VB Process() )

    once you have that process you might be able to use StandardInput and StandardOutput... still am a little unclear as to "what" your end goal is- either way the Process Class should help you out...

    why don't you modify the web app to use the excel data? MSDN Article talking about how to use excel as a datasource

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #9
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Thank you I will explore all that.

    Regarding my end goal... the current process is,
    search on the website/database front end...
    Go to the Excel sheet and trigger the Macro
    This uses AppActivate to focus back to the webpage and extract some info and puts it into Excl

    It then goes back to the webpage and puts in a new URL and pastes the data into relevant form fields.

    This last bit I've been doing with AppActivate too using SendKeys to tab around to the fields and paste the data into the field. Problem was any delay when loading the webpage would mess things up.
    So using this ie. bit of coding above works great except that's it's opening new Browser windows instead of doing it in the current one I'm using.

    I need
    Code:
    Set ie = New InternetExplorer
    to be tweaked so it's not a New InternetExplorer I want ie set to but the one that is called "This Title" or just current active one.


    I can't modify the web app unfortunately. I'm only at front end level for that one.

    I'll get there....

    Cheers

  • #10
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Oh, I've managed to get it working with a little help from a code I've found..

    This goes before the ie.navigate "http//myurl...."

    Code:
    Dim ie As Object
    
    With CreateObject("Shell.Application").Windows
    
    If .Count > 0 Then
    Set ie = .Item(0)
    Else
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    End If
    End With
    But I'm trying to now work out how and why it works...


    If you can explain this bit to me that would be great.
    Thanks for your help


  •  

    Posting Permissions

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