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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,119,300
Messages
5,577,263
Members
412,779
Latest member
Kings11Q
Top