asked on

CCan Excel VBA control the internet?

Experts!

I have a question to ask in reference to Excel VBA and Internet Explorer.
I use VBA Excel at work, and now my job has created a website that allows their employees to look up info that will increase our productivity. Basically it is a online database where you can sort info and download documents.

I have been given a task to go to this website "login" and look up document numbers which will display an picture. This picture needs to be placed back into Excel as a rollover comment.
So what Is there a way that I can write VBA macro that will call out to the website, enter in my Username and Password, hit enter, find the area on the screen to paste in the document number, hit enter, the select the picture, copy it to clipboard, then insert it back into excel as a roll over comment?

I know you can open up web pages using Excel but what about positioning data in fiels outside of the normal toolbar that is in Explorer?

Thank you all!!

jamesrh

This is possible but not easy. What you would need to do is construct the same post as the web page would in sending back the form with the textbox filled in using either Msxml2.ServerXMLHTTP or WinHttp.WinHttpRequest.5.1. You would then recieve the binary response and place it where you wanted it in Excel. For code samples I would search Msxml2.ServerXMLHTTP and VBA or WinHttp.WinHttpRequest.5.1. and VBA. There is a somewhat related EE post at https://www.experts-exchange.com/questions/23395981/HOw-to-Use-VBA-to-do-an-http-post.html

Maliki Hassani

ASKER

What about using MS WebBrowser contro? Have you heard of this?l

jamesrh

That would also be an option either code only or in a user form.

Maliki Hassani

ASKER

Can anyone provide me with the VBA code to open up explorer? I was thinking that when explorer is open, I can control it by telling the macro to activate the tab button to move around the webpage.

Brad Sims

Dim browser As Object

Set browser = CreateObject("InternetExplorer.Application")

With browser
browser.Navigate ("http://servicecenter.companyx.com")
End With

Brad Sims

I use SendKeys for our website in my macro, but I only have to control one movement and it's the same all the time.

Maliki Hassani

ASKER

Thank you for your response. I am not havbing any success with opening up exploer. Perhaps my references are not correct? Please see attached file.
EXCEL-REFERENCES.doc

Maliki Hassani

ASKER

Here is my code:

Sub Explorer()
Dim browser As Object

   Set browser = CreateObject("InternetExplorer.Application")

   With browser
   browser.Navigate ("http://www.Hotmail.com")
   
   End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Brad Sims

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maliki Hassani

ASKER

Awesome!

Any particular way to add in Send Keys?

Maliki Hassani

ASKER

So once the website is opened, I need it to tab key and paste in my name. Any idea on how to do this?
My beginners knowledge would have it go to sheet2 copy cell A1, move to cell A2 using offset feature, then paste in name, then tab over go back to sheet 2 copy my password, move to cell A3 using offset feature ..ETC

Any ideas ??

Brad Sims

You can set the username and password as strings so Excel doesn't have to look for them. Here is my full code that I used for one macro.

Sub Call_Report()

Dim browser As Object
Dim uname As String
Dim pword As String

Set browser = CreateObject("InternetExplorer.Application")

With browser
browser.Navigate ("http://servicecenter.companyx.com/")

.StatusBar = False
.Toolbar = True
.Visible = True
.Resizable = False
.AddressBar = True

uname = "cservice"
pword = "password"
nline = "{TAB}"
pass = "{ENTER}"

Application.Wait Now + TimeValue("00:00:04")

SendKeys uname, True

SendKeys nline, True

SendKeys pword, True

SendKeys nline, True

SendKeys pass, True

Application.Wait Now + TimeValue("00:00:20")

For i = 1 To 31
SendKeys nline, True
Next i

SendKeys pass, True

End With

End Sub