Question on a VBA code

enginkuru

New Member
Joined
Oct 11, 2006
Messages
5
Hi,

I try to copy a webpage into excel as I cant import the data on the webpage with excel web query ( I think the problem is that the data values are also link buttons). But when I copy paste the webpage to excel manually I get the data I need. I found a VBA code in this forum that should open the webpage, copy it all and then paste it to excel. But I always get an error message at
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

The problem is that I have almost no idea about VBA. Could anyone help me on this code? and I think when it works it pastes the page in text format but I would prefer html.....

Below is the code that should copy the google main page to excel.

Sub myWebOpenPW()

' Dim IE As New InternetExplorer
Dim IE As Object
Set IE = CreateObject("InternetExplorer.application")

'Make Internet Explorer visible and go to Website
IE.Visible = True
IE.Navigate "http://www.google.com"
Do
If IE.ReadyState = 4 Then
IE.Visible = False
Exit Do
Else
DoEvents
End If
Loop


' Wait for IE to load
Application.Wait (Now + TimeValue("0:00:10"))

' Select All Intenet Explorer data and then Copy to the clipboard
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("B2").Select
' Close Internet Explorer
IE.Visible = False
End Sub
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,112,883
Messages
5,543,019
Members
410,583
Latest member
gazz57
Top