VBA Script to activate a webpage and copy it

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
canesbrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncanb7Commented:
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
canesbrAuthor Commented:
On the Function statement I get Compile Error User-defined type not defined.
REgards
Brian
0
duncanb7Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

canesbrAuthor Commented:
I am not following you
Please identify compile error.
Regards
Brian
0
duncanb7Commented:
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
canesbrAuthor Commented:
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
duncanb7Commented:
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
canesbrAuthor Commented:
That compiles ok thanks
This addresses IE and HTML
How would I adjust for other browsers like Chrome?
Regards
Brian
0
duncanb7Commented:
Chrome/FF is NOT allowed for this method, only for IE on VBA.
You might make new thread for Chrome/FF


Duncan
0
canesbrAuthor Commented:
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
duncanb7Commented:
just w.Sendkeys("^a^c")
0
duncanb7Commented:
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
canesbrAuthor Commented:
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
duncanb7Commented:
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
duncanb7Commented:
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
NorieVBA ExpertCommented:
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
canesbrAuthor Commented:
Yes the original post said a search for a particular web page. After copying, it will be pasted into a worksheet.
Regards
Brian
0
thenelsonCommented:
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
aikimarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
canesbrAuthor Commented:
@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
canesbrAuthor Commented:
@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
aikimarkCommented:
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
canesbrAuthor Commented:
@Aikimark
Nope. Would you have an example I can work off of?
Regards
Brian
0
thenelsonCommented:
>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
aikimarkCommented:
    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
aikimarkCommented:
If you need some browser automation via VBA code, I can recommend Selenium.
0
canesbrAuthor Commented:
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
canesbrAuthor Commented:
Hereto attached
Regards
Brian
CaptureCannex.JPG
0
aikimarkCommented:
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
aikimarkCommented:
what is the exact URL, including application and program parameters that you are using?
0
canesbrAuthor Commented:
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
aikimarkCommented:
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
canesbrAuthor Commented:
0
aikimarkCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.