launch web browser, paste first value (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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

With above code in place, add the following routine which when run should do what you want

Sub LaunchWebPage()
    Dim strLaunch as String
    strLaunch = "www.website.com/~RequiredPage" ' Enter the full URL here
    Launch (strLaunch)
End Sub

Give it a spin and see if it works,

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

sorry, forgot to link the button to the form, try this one instead…

Reply | Quote