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

[Mike UK]

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

 

[John_w]

Try replacing the execCommand lines with:

    app.ExecWB 17, 0
    Application.Wait DateAdd("s", 1, Now)
    app.ExecWB 12, 2
    app.ExecWB 18, 0

 

[Anthony47]

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]

Try replacing the execCommand lines with:

    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]

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]

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

Set app = CreateObject("InternetExplorer.Application")

with

Dim app As InternetExplorer
Set app = New InternetExplorer

we can use defined constants instead of numbers, making the code self-explanatory:

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]

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

Set app = CreateObject("InternetExplorer.Application")

with

Dim app As InternetExplorer
Set app = New InternetExplorer

we can use defined constants instead of numbers, making the code self-explanatory:

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]

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

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]

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:

Dim app As InternetExplorerMedium
Set app = New InternetExplorerMedium

which needs the reference to Microsoft Internet Controls.