Excel & IE
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel & IE

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:


    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




    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-22 15:07 ]

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al....Great...thanks

    Ivan

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com