469,315 Members | 1,866 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to Copy Data From Website to Excel

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".

Expand|Select|Wrap|Line Numbers
  1. Public Sub csi()
  2. Dim appIE As InternetExplorer
  3. Dim xlApp As Excel.Application
  4. Dim wsData As Worksheet
  5. Set xlApp = Application
  6. Set wsData = xlApp.Worksheets("CSIInq")
  7. Set appIE = New InternetExplorer
  8. AppActivate ("Exact Website Title Name Here")
  9. appIE.ExecWB 17, 2  <<--- This line is where the error comes up
  10. appIE.ExecWB 12, 0
  11. AppActivate ("CSI")
  12. Workbooks("CSI.XLS").Activate
  13. Range("A1").Select
  14. ActiveSheet.Paste
  15. Set appIE = Nothing
  16.  
  17. End Sub
Sep 30 '10 #1
17 19127
MMcCarthy
14,534 Expert Mod 8TB
Have you set a reference to the library "Microsoft Internet Controls"?
Sep 30 '10 #2
Yes, that reference has been set
Sep 30 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
OK change this line
Expand|Select|Wrap|Line Numbers
  1. AppActivate ("Exact Website Title Name Here")
to
Expand|Select|Wrap|Line Numbers
  1. objIE.Navigate "Exact Website Title Name Here"
Does that help?
Sep 30 '10 #4
No, I get the same error as above on the same line (#9).
Sep 30 '10 #5
MMcCarthy
14,534 Expert Mod 8TB
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
Sep 30 '10 #6
Yes, the error is on #9. I just went searching and saw the same thing, but I was having trouble changing the registry
Sep 30 '10 #7
MMcCarthy
14,534 Expert Mod 8TB
I'm going to see if I can get someone with more experience using this library to check this out.

Mary
Sep 30 '10 #8
ADezii
8,800 Expert 8TB
Try the below as a Template:
Expand|Select|Wrap|Line Numbers
  1. 'First, you must set a Reference to the Microsoft Internet Controls
  2. Dim appIE As InternetExplorer
  3. Const conURL As String = "http://www.msn.com"
  4.  
  5. DoCmd.Hourglass True
  6.  
  7. Set appIE = New InternetExplorer
  8.  
  9. appIE.Navigate conURL
  10.  
  11. 'You must wait until the Page is fully loaded
  12. Do
  13. Loop Until appIE.ReadyState = READYSTATE_COMPLETE
  14.  
  15. 'Copy and Paste the Web Page
  16. appIE.ExecWB 17, 2
  17. appIE.ExecWB 12, 0
  18.  
  19. 'Web Page should now be in the Clipboard
  20.  
  21. DoCmd.Hourglass False
  22.  
  23. 'Clean Up, being careful of Scope
  24. 'appIE.Quit
  25. 'Set appIE = Nothing
Sep 30 '10 #9
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.
Oct 1 '10 #10
ADezii
8,800 Expert 8TB
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.
Oct 1 '10 #11
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?
Oct 1 '10 #12
ADezii
8,800 Expert 8TB
Is it an absolute requirement that the Window already be Open?
Oct 1 '10 #13
Yes because it's a secure website so I wouldn't be able to access it from just entering the URL
Oct 1 '10 #14
ADezii
8,800 Expert 8TB
Back to the Drawing Board, you may have a problem...
Oct 1 '10 #15
Denburt
1,356 Expert 1GB
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
Oct 7 '10 #16
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
Oct 8 '10 #17
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
Nov 22 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Faith | last post: by
1 post views Thread by Steven Stewart | last post: by
5 posts views Thread by Jonny | last post: by
reply views Thread by Sonnich | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.