Excel & IE

[ctcoach141]

Can someone tell me how to open an Internet Explorer page, select the entire page (ctrl a)copy it to the clip board (ctrl c) and return to EXCEL and paste (ctrl v) onto a worksheet. This, of course, all must be done from a signle click on an existing link in EXCEL.

 

[Mark O'Brien]

Do you mean you want a VBA macro to do all of this for you?

To literally do what you just described you're going to have to use "SendKeys" and "AppActivate". That's assuming you can hardcode the IE title bar text.

 

[Al Chara]

Mark, I too had the same idea to use sendkeys, but can't get it to work.

ActiveWorkbook.FollowHyperlink Address:="http://sports.yahoo.com/"
AppActivate "Microsoft Internet Explorer"
SendKeys "^A"
SendKeys "^C"
AppActivate "Microsoft Excel"
ActiveSheet.Paste

Any ideas...

 

[Mark O'Brien]

You have to have the exact title in IE for this to work. Personally I would probably look into using API calls to get the handle of the IE window, but I don't have that code in work. Anyway, for AppActivate and Sendkeys to work you need to put pauses to let the computer catch up.

this code works for me:<pre>
ActiveWorkbook.FollowHyperlink Address:="http://www.soccernet.com/"
Application.Wait (Now + TimeValue("0:00:05"))
AppActivate "ESPN.com Soccernet: Home - Microsoft Internet Explorer provided by American Electric Power"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "%E"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "A"
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "^C"
Application.Wait (Now + TimeValue("0:00:01"))
AppActivate "Microsoft Excel - Book2"
Application.Wait (Now + TimeValue("0:00:05"))
ActiveSheet.Paste</pre>



_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-22 15:07

 

[Al Chara]

I got that to work too, but it still doesn't solve the problem of going to a different website. I'm still working on the API call part (I am trying to do it with the class name, not the title). Do you know what the class name is for Internet Explorer?
This message was edited by Al Chara on 2002-03-22 16:33

 

[Ivan F Moala]

On 2002-03-22 16:32, Al Chara wrote:
I got that to work too, but it still doesn't solve the problem of going to a different website. I'm still working on the API call part (I am trying to do it with the class name, not the title). Do you know what the class name is for Internet Explorer?
This message was edited by Al Chara on 2002-03-22 16:33

Class name for IE = IEFrame
How have you called the routine.....
Have you Shelled out to it.....??


Ivan

 

[Al Chara]

Hey guys,

Try the following code:

Option Explicit

Public Const SW_RESTORE As Long = 9&
Public Const GW_CHILD As Long = 5&
Public Const GW_HWNDNEXT As Long = 2&

Declare Function GetDesktopWindow& Lib "user32" ()
Declare Function GetWindow& Lib "user32" (ByVal hWnd&, ByVal wCmd&)
Public Declare Function GetWindowText& Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd&, ByVal lpString$, ByVal cch&)
Declare Function ShowWindow& Lib "user32" (ByVal hWnd&, ByVal nCmdShow&)
Public Declare Function SetForegroundWindow& Lib "user32" (ByVal hWnd&)
Sub Main()
Const BaseCaption As String = "Internet Explorer"
Dim AppWindow&, sTemp$, StartTime, CycleTime

StartTime = Now
AppWindow = GetWindow(GetDesktopWindow(), GW_CHILD)
Do While CycleTime - StartTime< TimeValue("00:00:01")
sTemp = String$(180, False)
Call GetWindowText(AppWindow, sTemp, 179)
If InStr(sTemp, BaseCaption) Then
ActivateWindow AppWindow
Exit Sub
End If
AppWindow = GetWindow(AppWindow, GW_HWNDNEXT)
CycleTime = Now
Loop
MsgBox "The window is not open"
End Sub
Private Sub ActivateWindow(ByVal AppWindow&)
Call ShowWindow(AppWindow, SW_RESTORE)
Call SetForegroundWindow(AppWindow)
End Sub

It searches for the string "Internet Explorer" in the title of all open windows. If one of the titles is a match it activates that window, if there are now matches then it returns a message saying there were no matches. (Got some of the code floating on the internet)

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-03-22 18:28

 

[Ivan F Moala]

Al....Great...thanks

Ivan

 

[Ivan F Moala]

Al

I Changed Public const to include

Public Const SW_MAXIMIZE As Long = 3&

Plus I changed call to MAX window;

Call ShowWindow(AppWindow, SW_MAXIMIZE)


As I want the window Maximized.


Ivan
This message was edited by Ivan F Moala on 2002-03-23 03:22

 

[ctcoach141]

Hey! Thanks for all the replies to my question! I'll let you know how it works!

Well, After working out some of the details that AppActivate and SendKeys works great! Thanks to Al & Brain!

Here's another problem for you. How can I send a URL string to the address window after navigating to a web page from an Excel Hyperlink?
This message was edited by ctcoach141 on 2002-03-26 06:08