VBA Copy Internet Explorer webpage and paste into Excel using Match Destination Formatting

Mike UK

New Member
Joined
Dec 14, 2011
Messages
39
I have a code that opens various web pages, copies (Select-All) the information on the web page and then pastes the information into an excel using match destination formatting to ensure that the worksheet replicates the column formatting within the web pages. The code below has worked for months but has stopped working. The web page is no longer copied in a form that permits the required paste function. If I manually Select-All and Control-C to copy then I can paste as required. However, the code still works for a colleague without issue.

I have checked my reset my IE settings to that of my colleague yet it still doesn't work.

The code is as follows.....
Set app = CreateObject("InternetExplorer.Application")
'Creates IE Webpage that the user is logged in and goes to the specific model web page
With app
.Visible = True
.Navigate WebAdd
Do Until .ReadyState = 4
DoEvents
Loop
End With

'Selects the full webpage and copies the contents
app.document.execCommand "SelectAll", False
app.document.execCommand "Copy", False
app.Quit 'closes IE page

Worksheets("Download").Activate

Range("A1").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
ActiveSheet.Cells.WrapText = False
ActiveSheet.Cells.MergeCells = False


IE 11 version 1909 (OS Build 18363.1379)
Any ideas why my IE no longer allows the copying in the correct format or amended coding that would complete the same task? Thanks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,748
Try replacing the execCommand lines with:
VBA Code:
    app.ExecWB 17, 0
    Application.Wait DateAdd("s", 1, Now)
    app.ExecWB 12, 2
    app.ExecWB 18, 0
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
The macro behave as expected on my Excel 365 + IE 11
The first hypotesis to check is whether the page can be manually selected, copied and pasted; without the url I cannot do more

Bye
 

Mike UK

New Member
Joined
Dec 14, 2011
Messages
39
Try replacing the execCommand lines with:
VBA Code:
    app.ExecWB 17, 0
    Application.Wait DateAdd("s", 1, Now)
    app.ExecWB 12, 2
    app.ExecWB 18, 0
Thanks John will give it a go over the weekend. Can I ask what the code does as I'm not familiar with 17,0 or 12,2, or 18,0.
 

Mike UK

New Member
Joined
Dec 14, 2011
Messages
39

ADVERTISEMENT

The macro behave as expected on my Excel 365 + IE 11
The first hypotesis to check is whether the page can be manually selected, copied and pasted; without the url I cannot do more

Bye
Hi Antony, the code works fine on my colleagues machine too (using the same websites (& URLs that I use). I can manually select all of the web page, copy then paste using match destination formatting and all works fine.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,748
Can I ask what the code does as I'm not familiar with 17,0 or 12,2, or 18,0.
Select all, copy and clear selection.

Your code uses late binding of the InternetExplorer object. With early binding - add reference to MS Internet Controls - replace
VBA Code:
Set app = CreateObject("InternetExplorer.Application")
with
VBA Code:
Dim app As InternetExplorer
Set app = New InternetExplorer
we can use defined constants instead of numbers, making the code self-explanatory:
VBA Code:
app.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
Application.Wait DateAdd("s", 1, Now) 
app.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
app.ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
 

Mike UK

New Member
Joined
Dec 14, 2011
Messages
39

ADVERTISEMENT

Select all, copy and clear selection.

Your code uses late binding of the InternetExplorer object. With early binding - add reference to MS Internet Controls - replace
VBA Code:
Set app = CreateObject("InternetExplorer.Application")
with
VBA Code:
Dim app As InternetExplorer
Set app = New InternetExplorer
we can use defined constants instead of numbers, making the code self-explanatory:
VBA Code:
app.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
Application.Wait DateAdd("s", 1, Now)
app.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
app.ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
Brilliant, thanks so much for taking the trouble to explain the terminology. 👍🏻
 

Mike UK

New Member
Joined
Dec 14, 2011
Messages
39
Brilliant, thanks so much for taking the trouble to explain the terminology. 👍🏻
Hi John_w
Apologies for the delay, work has been busy so only just got round to trying the code.
When I run the new code I get the error....


1616331961746.png


Additionally, I had to amend the first lines of code to be...

Dim app As Object 'new
Set app = CreateObject("InternetExplorer.Application") 'new

rather than:
Dim app As InternetExplorer
Set app = New InternetExplorer

Any ideas greatly appreciated.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,748
You needed to add a reference to Microsoft Internet Controls, via Tools -> References in the VBA editor, for my code to work, or at least compile.

For the error try adding the site to IE's Trusted Sites. If the error persists:
VBA Code:
Dim app As InternetExplorerMedium
Set app = New InternetExplorerMedium
which needs the reference to Microsoft Internet Controls.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,346
Messages
5,635,758
Members
416,877
Latest member
SolarTheory

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top