How to Copy Data From Website to Excel

[epifinygirl]

Hi,

I'm relatively new to VBA but I am trying to copy data from a website (this website is currently running and will be the same) and paste to defined sheet in Excel. Below is the code that I have thus far, however, I am receiving an error "Method 'ExecWB' of object 'IWebBrowser2' failed".

Public Sub csi()
Dim appIE As InternetExplorer
Dim xlApp As Excel.Application
Dim wsData As Worksheet
Set xlApp = Application
Set wsData = xlApp.Worksheets("CSIInq")
Set appIE = New InternetExplorer
AppActivate ("Exact Website Title Name Here")
appIE.ExecWB 17, 2  <<--- This line is where the error comes up
appIE.ExecWB 12, 0
AppActivate ("CSI")
Workbooks("CSI.XLS").Activate
Range("A1").Select
ActiveSheet.Paste
Set appIE = Nothing
 
End Sub

[MMcCarthy]

Have you set a reference to the library "Microsoft Internet Controls"?

[epifinygirl]

Yes, that reference has been set

[MMcCarthy]

OK change this line

AppActivate ("Exact Website Title Name Here")

to

objIE.Navigate "Exact Website Title Name Here"

Does that help?

[epifinygirl]

No, I get the same error as above on the same line (#9).

[MMcCarthy]

Are you sure the error is happening at line #9 and not line #10. Check out the remarks section on this method on the msdn website.

http://msdn.microsoft.com/en-us/libr...=VS.85%29.aspx

[epifinygirl]

Yes, the error is on #9. I just went searching and saw the same thing, but I was having trouble changing the registry

[MMcCarthy]

I'm going to see if I can get someone with more experience using this library to check this out.

Mary

[ADezii]

Try the below as a Template:

'First, you must set a Reference to the Microsoft Internet Controls
Dim appIE As InternetExplorer
Const conURL As String = "http://www.msn.com"
 
DoCmd.Hourglass True
 
Set appIE = New InternetExplorer
 
appIE.Navigate conURL
 
'You must wait until the Page is fully loaded
Do
Loop Until appIE.ReadyState = READYSTATE_COMPLETE
 
'Copy and Paste the Web Page
appIE.ExecWB 17, 2
appIE.ExecWB 12, 0
 
'Web Page should now be in the Clipboard
 
DoCmd.Hourglass False
 
'Clean Up, being careful of Scope
'appIE.Quit
'Set appIE = Nothing

[epifinygirl]

Great!! Is it possible to change the constant URL to activating the open window with a specific title? I ask because the website is based off of several logins and if I try to retrieve it another way, the session will time out.

[ADezii]

You can change the URL to anything you like, but to the best of my knowledge, a New Instance of IE will be opened when Navigating to this URL.

[epifinygirl]

Well, the website will already be open since I'll be logged in and if I put the URL in the code then it will give me a "session expired" message. There is no way to switch windows to copy the data and then come back to paste it?

[ADezii]

Is it an absolute requirement that the Window already be Open?

[epifinygirl]

Yes because it's a secure website so I wouldn't be able to access it from just entering the URL

[ADezii]

Back to the Drawing Board, you may have a problem...

[Denburt]

After rereading this thread several times I thought I might throw my two cents in...

There are a number of routines that have been written that can grab the handle of the the internet explorer window and return the information you are requesting yet your understanding of VBA may make this difficult to accomplish.

Have you considered using a Web query? You can use the import window to view the page then log in once you log in select the info you want and go from there. I know this method has some security issues so you may want to consider that when using it with passwords and other sensitive information. I have used this method for log in pages in the past and had decent results the pitfall of this is when you need to log in you will need to edit the Web query and use the wizards window to log in. I hope this helps, let us know.

Here is some more info on the Web query:Web queries

[epifinygirl]

I have used Web queries in the past and I tried with this but it tells me that the "session expired". I haven't tried logging in through the Web Query window because there are different pop-ups that you will need to get open when logging in so I figured that because of that issue, web queries wouldn't work

[Matt V]

Try flipping from this:
appIE.ExecWB 17, 2
appIE.ExecWB 12, 0

to this:
IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection