Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    launch web browser, paste first value (Excel 97)

    Aloha all,

    I'm adding a little bell or maybe half a whistle to my Excel phone directory. I'm trying to set up a quick jump to a web-based text paging utility that most of our staff are registered on.

    The idea is that user clicks a button, and for the directory entry on the row where the active cell is, the individual's pager number is copied to the clipboard, the user's web browser launches and pager number is pasted into the only edit field.

    What I'd like to do, and can't figure my way past, is make it so the pager number is automatically entered or pasted, saving user the tedium of another menu click or pressing Ctrl-V. It's always the little things. The FollowHyperlink method launches the browser great but I cannot get the pager number in. my code snippet is brief and simple, as follows:

    Cells(ActiveCell.Row, 8).Copy ' pager numbers are in Column H
    Application.CutCopyMode = False
    ActiveWorkbook.FollowHyperlink _
    Address:="http://www.arch.com/message/", _
    NewWindow:=True

    ...so then what? Excel loses focus. SendKeys doesn't work. Other FollowHyperlink parameters don't work. Any Ideas?

    Would DDE work, and if so, what would Internet Explorer's appname be?

    Mahalo in advance for your comments and suggestions,

    JohnJ

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    Do you have access to the code behind the text-paging utility? The easiest way would be to make the page accept a parameter, which would make you URL look something like "http://www.arch.com/message/page.htm?number=0777123456" and then display that number in the text box by default.

    If not, if your users are using IE, you could try playing with the Internet Explorer object library. This would involve creating an Browser object, making it visible, navigating to a page,and setting the value, etc.

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    London
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    Hi,

    I have done this before in two ways :

    Set the hyperlink base in File Properites and then use the hyperlink worksheet formula to link to the pager number

    Or the following simple macro works very well for me :

    Sub WebPage()
    i = ActiveCell
    AppActivate "Internet Explorer"
    SendKeys "{TAB}"
    SendKeys "http://www.WEBSITE.com/" & i & "~"

    AjUK

  4. #4
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    Thanks AJuk,

    but "Internet Explorer" does not work as an appname for either the Shell() function (to launch IE) or the AppActivate() statement (with IE running already), nor does any variation I've tried. If I can find the right name, I think the SendKeys statement would work to get me past the first page where the pager number is input, maybe farther. Where does one find this appname?

    And Thanks Adam,

    I don't have access to the code behind the web site paging. Via email I've asked the subject website's support for help, nothing yet.

    I like your suggestion about creating and manipulating an Internet Explorer object! Unfortunately for me, I'm horribly uneducated/inexperienced with that. If I could find a good example or two of code referencing the right library and creating an MSIE object, I could muddle my way through it I think. But I don't know where to start.

    Mahalo for everyone's continued help,

    John Jacobson

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    John,

    You could try an API call, ShellExecute if you know the full Url you wish launch. The following code should Launch your browser, and open the Url you pas to it. <pre>Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Sub Launch(ByVal strLaunch As String)
    Call ShellExecute(0&, "open", strLaunch, vbNullString, vbNullString, vbNormalFocus)
    End Sub</pre>

    With above code in place, add the following routine which when run should do what you want <pre>Sub LaunchWebPage()
    Dim strLaunch as String
    strLaunch = "www.website.com/~RequiredPage" ' Enter the full URL here
    Launch (strLaunch)
    End Sub</pre>

    Give it a spin and see if it works,

    Andrew C

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    Thanks Andrew,

    Your code opens the browser just fine, but my problem really begins there. Initially, I want to input a value in the web page's single edit field. Ultimately, I want to maintain control of the browser and continue to send commands, via SendKeys method or some other, to achieve what I want -- a trouble-free, simplified access to this paging utility from my Excel phone directory for even the most challenged Joe User.

    I tried adding the SendKeys method within LaunchWebPage() but nothing happened. Any other suggestions? How to I keep from losing focus?

    mahalo for your feedback,

    JohnJ

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    See the attached file for an example of IE automation from Excel.

    I only looked at this briefly, and I'm not sure if it can be done without dropping the control onto a form, but this example could be manipulated to browse to your paging website, and, once you know the names of the HTML elements you need to fill in (from looking at the pages source) you can reference those items just as I have referenced the input box ("q") and submit button ("btnG"). Your code could open this form, fill in the text boxes, submit the page and close the form.

    Hope this helps,
    Adam
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: launch web browser, paste first value (Excel 97)

    sorry, forgot to link the button to the form, try this one instead...
    Attached Files Attached Files

Posting Permissions

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