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/library/aa752087%28v=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...