?
Solved

VBA Script to activate a webpage and copy it

Posted on 2014-07-11
36
Medium Priority
?
6,586 Views
Last Modified: 2014-08-07
Looking for an Excel VBA script that would search for an open browser page window (Chrome or IE tab) by caption or URL and do an Application.Sendkeys("^a^c")
Regards
Brian
0
Comment
Question by:canesbr
  • 14
  • 9
  • 8
  • +2
34 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40192653
The following code is getting browser object from searching browser title that
is easier way to solve your question. You can modify the code for your need if search  from URL(address bar)
Proabably, you can put  w.Sendkeys("^a^c") for a try. It might be a good start to solve your
issue

Hope understand your question completely.If not, please point it out

Duncan
Function IEWindowFromTitle(sTitle As String) As SHDocVw.InternetExplorer

    Dim objShellWindows As New SHDocVw.ShellWindows
    Dim win As Object, rv As SHDocVw.InternetExplorer

    For Each win In objShellWindows
        If TypeName(win.Document) = "HTMLDocument" Then
            If UCase(win.Document.Title) = UCase(sTitle) Then
                Set rv = win
                Exit For
            End If
        End If
    Next

    Set IEWindowFromTitle = rv

End Function

Sub Tester()

    Dim w As SHDocVw.InternetExplorer
    Set w = IEWindowFromTitle("Google")
    If Not w Is Nothing Then
        Debug.Print w.Document.Title
    Else
        Debug.Print "Not found"
    End If

End Sub

Open in new window

0
 

Author Comment

by:canesbr
ID: 40193165
On the Function statement I get Compile Error User-defined type not defined.
REgards
Brian
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40193169
Please work around the code is okay but not tested
And it will work after pass those typing error, at least give you
idea and have a good start
Duncan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:canesbr
ID: 40193257
I am not following you
Please identify compile error.
Regards
Brian
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40193492
When you open IE browser, it will show the title of that browser at the most top and right corner that is exactly  same as  the string in <title>Google</title> tag when the browser address bar is going to http://www.google.com and that will assist the VBA program to find out the browser object,For example, when IE go to www.google.com, the title tag is "Google" and then Set w = IEWindowFromTitle("Google") will find out the IE object, and  stored it at w, when you  try w.Sendkeys("^a^c"), it will send the key to  IE object that belong to the tiltle with Google.
you can start  code and run Tester() subroutine for a start to see any compiling error first
(Note: You can view the webpage source code from www.google.com
and search title tag that show the string of "Google")


Duncan
0
 

Author Comment

by:canesbr
ID: 40194393
I apologize for not understanding.
You posted some code.
I copied and pasted it into a module
I tried to run it and I got a compile error.
"User-defined type not defined."
Which to me means there is something wrong with the code.
What exactly is wrong with the code?
Regards
Brian
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194488
1- need to define Global w As SHDocVw.InternetExplorer on the top
2- On VBA editor, you need to add MS internet library for using SHDocVw.inernetExplorer library
just do, click atl+11 going to VBC editor -->tools->macro libarary(first item in tools)->select
and check two options, one is called "Microsoft Internet Control" and other is "Microsoft HTML Object Library"

Be remined, it is reference code only for your start to solve issue, you need to debug it

Duncan

Global w As SHDocVw.InternetExplorer
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Function IEWindowFromTitle(sTitle As String) As SHDocVw.InternetExplorer

    Dim objShellWindows As New SHDocVw.ShellWindows
    Dim win As Object, rv As SHDocVw.InternetExplorer

    For Each win In objShellWindows
        If TypeName(win.Document) = "HTMLDocument" Then
            If UCase(win.Document.Title) = UCase(sTitle) Then
                Set rv = win
                Exit For
            End If
        End If
    Next

    Set IEWindowFromTitle = rv

End Function
Sub Tester()

    Dim w As SHDocVw.InternetExplorer
    Set w = IEWindowFromTitle("Google")
    If Not w Is Nothing Then
        Debug.Print w.Document.Title
    Else
        Debug.Print "Not found"
    End If

End Sub

Open in new window

0
 

Author Comment

by:canesbr
ID: 40194572
That compiles ok thanks
This addresses IE and HTML
How would I adjust for other browsers like Chrome?
Regards
Brian
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194590
Chrome/FF is NOT allowed for this method, only for IE on VBA.
You might make new thread for Chrome/FF


Duncan
0
 

Author Comment

by:canesbr
ID: 40194624
Aha.
I mentioned Chrome in the original post.
Ok, No problem
Just one thing - If I am going to do a sendkeys I need the found window to have the focus. How?
Regards
Brian
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194786
just w.Sendkeys("^a^c")
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194988
Dim HWNDSrc As Long
HWNDSrc = w.HWND
SetForegroundWindow HWNDSrc  'set the IE object on the foreground on the screen
w.Sendkeys("^a^c")

Try this

Duncan
0
 

Author Comment

by:canesbr
ID: 40195128
In the statement w.sendkeys, I get Run-time error '438': Object doesn't support the property or method.
If I use Application.sendkeys, the VBA code gets selected and copied.
Please advise
Regards
Brian

Note: I added
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40195136
yes you are right, you need to add Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

application.sendkeys will be okay, as I know from memory, I need to
do to setforeground at the sendkeys with object handler

anway, it is closer to solve all your issue

Be reminded, those code is for reference only you need to debug it

Duncan
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40195149
I think other experts could help on this thread if you click "attention request' on this thread

Have a nice day , talk you tomorrow

Duncan
0
 
LVL 35

Expert Comment

by:Norie
ID: 40206489
Brian

What do you want to do with what you are copying from the browser window?

WIll the browser be showing a particulare page/site?
0
 

Author Comment

by:canesbr
ID: 40206536
Yes the original post said a search for a particular web page. After copying, it will be pasted into a worksheet.
Regards
Brian
0
 
LVL 39

Expert Comment

by:thenelson
ID: 40231566
This sample database will show you how to transfer information to and from the internet using: The Web Browser control, Internet Explorer Automation and FTP - File Transfer Protocol. It also contains many functions useful for interfacing with the internet.  http://thenelson.name/#WebAccess

You should not need to use sendkeys. You should be able to copy the resultant page with
documentElement.Innerhtml
documentElement.Outerhtml
documentElement.Innertext
documentElement.OuterText
depending upon what you want to copy.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40231663
There's no need for an API call
On Error Resume Next
AppActivate "Caption of browser window"
If Err =  0 Then
    SendKeys "^a^c"
End If

Open in new window

0
 

Author Comment

by:canesbr
ID: 40237633
@aikimark
The AppActivate appears to activate something..... but the send keys doesn't seem to work in this instance
AppActivate "MSN.com - Windows Internet Explorer"
SendKeys "^a^c"
So the appactivate works for IE
But, how to determine the app or caption for apps like Chrome or Firefox that don't show the normal titlebar. That is why I wanted to loop through and find the app instance (and tab) name that contains the text I am looking for.
Regards
Brian
0
 

Author Comment

by:canesbr
ID: 40237639
@TheNelson
I am looking to do this in Excel VBA.
Are you saying that there are Access VBA routines in your link that I could use in Excel VBA?
Are the routines restricted to Internet Explorer?
I looked but did not see.
Regards
Brian
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40237712
I supplied a generic solution.  Your environment may not be suitable.

Other than sending keys to copy the page, have you considered using the MSXML2.XMLHTTP object?
0
 

Author Comment

by:canesbr
ID: 40237835
@Aikimark
Nope. Would you have an example I can work off of?
Regards
Brian
0
 
LVL 39

Expert Comment

by:thenelson
ID: 40237841
>Are you saying that there are Access VBA routines in your link that I could use in Excel VBA?
The routines should work in Excel with very little modification.

>Are the routines restricted to Internet Explorer?
Yes, FF and Chrome do not support automation so interfacing with them via VBA is a lot more complicated.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40237849
    Dim oXMLHTTP As Object
    Dim strHTML as string
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", "http://yourtargetwebsiteURL", False
    oXMLHTTP.Send
    
    Do Until oXMLHTTP.ReadyState = 4
        DoEvents
    Loop
    
    If oXMLHTTP.Status = 200 Then
        strHTML = oXMLHTTP.responsetext
        'add your parsing code here
        'and then push the result into your worksheet
    End If

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40237852
If you need some browser automation via VBA code, I can recommend Selenium.
0
 

Author Comment

by:canesbr
ID: 40247128
Hi @aikimark
That kind of works if the page is all in HTML
I have a page that returns the string below, but the page actually looks like the attached.
So I would need a Sendkeys ^a^c
But when I tried that the focus was not on the web page so it copied the VBA script.
Is there a way to get the focus on the webpage so that the sendkeys would copy that webpage?
Regards
Brian

?strHTML
<!DOCTYPE html>
<!-- CANNEX UID:71bae71a-1954-43b0-b74e-3684cd938a07 -->
<!-- This template is used for specific errors -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<link rel="stylesheet" type="text/css" href="/style.css" />
<title>CANNEX Financial Exchanges Limited</title>
</head>
<body>
<hr />
<img src="/Autobahn/images/swsexcl.gif" alt="" />

<script type="text/javascript" language="javascript">
switch(12001)
{
  case 2210:
    document.write("<p>The system is currently unavailable. Please try again in 5 minutes.</p>")
    document.write("<p>Le syst&egrave;me n'est pas fonctionnel pour l'instant. Veuillez essayer &agrave; nouveau dans 5 minutes.</p>")
    break
  case 2618:
    document.write("<p>The system is currently busy, please try again.</p>")
    break
  case 12001:
    document.write("<p>You must specify an application name in the URL. (Application=...)</p>")
    document.write("<p>Please note that accessing this page using a bookmark will not work.</p>")
    break
  default:
    document.write("<p>You must specify an application name in the URL. (Application=...)</p>")
}
</script>

<br />
Reference: 12001<br />
Version: 5.01.00  Build 2
<hr />
</body>
</html>
0
 

Author Comment

by:canesbr
ID: 40247135
Hereto attached
Regards
Brian
CaptureCannex.JPG
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40247158
I think I should alert you to the possibility that the web page you linked to is a .exe and might run the risk of malware.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40247182
what is the exact URL, including application and program parameters that you are using?
0
 

Author Comment

by:canesbr
ID: 40247205
No, it's not malware, it's a legitimate pay to use website.
You need a userid and password to access. I think I cannot post that.
If possible you could do a join.me and take a look at it on my computer?
Regards
Brian
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40247258
That's possible.  You will probably have to use the msxml2.serverhttp object if we want to glom those pages' data without actually using a browser.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40247350
Note to future readers: Stepping through code that does windows automation (appactivate and sendkeys) does not work.  You have to execute the code either from the immediate window or from the menus.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Gain an elementary understanding of Blockchain technology.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question