Efficient paste from web page; also bring Excel to front

[tmwilkin]

I am copying text from a web page, then pasting it into an Excel file. The
below code gets the job done using SENDKEYS and activesheet.paste. However,
depending on the computer I'm using, activesheet.paste does not work ALL the
time (usually the first time it errors out, then subsequent times it's ok).
I am trying to find a better way to paste the data. Paste special doesn't
give me many options (I tried paste as text, but it crashed Excel).
Additionally, if anyone knows of a way to bring Excel to the front again in a
more effective manner than <Alt-Tab> through sendkeys, that would be
appreciated as well.

Here is the code:
Sheets("Data").Select
Range("B:E").ClearContents

SendKeys "^(a)", False
SendKeys "^(c)", False
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "%({TAB})", False

Range("B1").Select
ActiveSheet.Paste

 

[Bill Renaud]

<<Additionally, if anyone knows of a way to bring Excel to the front again
in a more effective manner than <Alt-Tab> through sendkeys, that would be
appreciated as well.>>

Try at the end of your routine (untested):

Application.Windows(1).Activate

 

[tmwilkin]

I tried "Application.Windows(1).Activate", however, with no success. It just
kept the web page as the active window. Thanks for the suggestion though.

Todd

 

[Dave Peterson]

Untested...

AppActivate Application.Caption

I tried "Application.Windows(1).Activate", however, with no success. It just
kept the web page as the active window. Thanks for the suggestion though.

Todd

 

[Bill Renaud]

OK, I did a little more work to check into this, as this is something that
I will be able to use in the future myself

Assuming that the IE window with the desired data is already being
displayed, try the following routine to simply select all of the data in
the IE window, copy it to the clipboard, then paste it onto a worksheet. (I
used the crime stats page from the Houston Police dept. for my neighborhood
as an example
http://www.houstontx.gov/police/cs/stats2007/oct07/oct075f40.htm)

Make sure that you pull down the Tools menu and select References, then add
a reference to "Microsoft Internet Controls" first (I am running Excel 2000
on Windows ME; it might be different on your system).

If the IE window is not already displayed, then I think you would use
CreateObject, instead of GetObject.

'----------------------------------------------------------------------
'References required:
' Microsoft Internet Controls
' C:\WINDOWS\SYSTEM\SHDOCVW.DLL

Public Sub Test()
'Declare Excel variables.
Dim xlApp As Excel.Application
Dim wsData As Worksheet

'Declare IE Browser variables.
Dim IEApp As InternetExplorer

'Set variables to Excel first.
Set xlApp = Application
Set wsData = xlApp.Worksheets("Data")

'Set variables to the browser window.
Set IEApp = GetObject(, "InternetExplorer.Application")

'Fetch the data from the Internet Explorer window.
With IEApp
.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
End With

'Re-activate Excel and paste data onto the worksheet.
AppActivate xlApp
wsData.PasteSpecial _
Format:="HTML", _
Link:=False, _
DisplayAsIcon:=False
End Sub

 

[codex]

use this
'' Because GetObject(, "InternetExplorer.Application") return
Err.Number = 429

Public Sub Test()
'Declare Excel variables.
Dim xlApp As Excel.Application
Dim wsData As Worksheet

'Declare IE Browser variables.
Dim IEApp As InternetExplorer

'Set variables to Excel first.
Set xlApp = Application
Set wsData = xlApp.Worksheets("Data")

'Set variables to the browser window.

Set IEApp = New InternetExplorer
IEApp.Visible = True
IEApp.Navigate "http://www.houstontx.gov/police/cs/stats2007/oct07/
oct075f40.htm"

'Fetch the data from the Internet Explorer window.
With IEApp
.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
End With

'Re-activate Excel and paste data onto the worksheet.
AppActivate xlApp
wsData.PasteSpecial _
Format:="HTML", _
Link:=False, _
DisplayAsIcon:=False
End Sub

 

[codex]

use this
'' Because GetObject(, "InternetExplorer.Application") return
Err.Number = 429

Public Sub Test()
'Declare Excel variables.
Dim xlApp As Excel.Application
Dim wsData As Worksheet

'Declare IE Browser variables.
Dim IEApp As InternetExplorer

'Set variables to Excel first.
Set xlApp = Application
Set wsData = xlApp.Worksheets("Data")

'Set variables to the browser window.

Set IEApp = New InternetExplorer
IEApp.Visible = True
IEApp.Navigate "http://www.houstontx.gov/police/cs/stats2007/oct07/
oct075f40.htm"

'Fetch the data from the Internet Explorer window.
With IEApp
.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
End With

'Re-activate Excel and paste data onto the worksheet.
AppActivate xlApp
wsData.PasteSpecial _
Format:="HTML", _
Link:=False, _
DisplayAsIcon:=False
End Sub

 

[Bill Renaud]

This is if IE is not already running. My code works for IE already running,
if you have already navigated to the page, as noted in my post.