VBA to copy data from a webpage in iexplore 1

[pd2004]

Hello,

I have pieced together the code below to navigate to a webpage and copy the information on it. I am reduced to using sendkeys and I really don't like it. If you could suggest code that could allow me to copy the data on the page instead of the sendkeys below I would really appreciate it.

On another note, you can see that the code tries to close iexplorer each time it is run. I would much rather keep it open and be able to refer to that application/window. I would appreciate any help there too.

You guys are a great help. This is my first attempt at iexplorer via VBA and I am finding it much more challenging than the Excel/Access that I am used to.

Thank you,

pd2004

Sub loadsheet(siteaddress)
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate siteaddress
objIE.Visible = 1
Do While (objIE.Busy)
Loop

Do Until objIE.ReadyState = 4
Application.Wait (Now + TimeValue("0:00:1"))
Loop
Do While objIE.Document Is Nothing
Application.Wait (Now + TimeValue("0:00:1"))
Loop
Do While objIE.Document.Body Is Nothing
Application.Wait (Now + TimeValue("0:00:1"))
Loop
Do While objIE.Busy ' is nothing
Application.Wait (Now + TimeValue("0:00:1"))
Loop
SendKeys "%EA", True
Application.Wait (Now + TimeValue("0:00:3"))
SendKeys "%EC", True
SendKeys "%FC", True
End Sub

 

[strongm]

Public Sub LoadSheet(SiteAddress As String)
Dim MrClipBoard As New DataObject
With CreateObject("InternetExplorer.Application") ' assuming that you really do want to late bind ...
.navigate SiteAddress
Do Until .readyState = 4 'READYSTATE_COMPLETE
DoEvents
Loop
MrClipBoard.SetText .document.body.innerText
MrClipBoard.PutInClipboard
End With
End Sub

 

[pd2004]

Thank you so much strongm! Super helpful!! I learned a lot from the example you gave. Say, can you clarify what you mean by late bind? I will be researching on my own, but if you think another way of doing this is better, I appreciate your input.

Thank you,

Pat

 

[pd2004]

Hi strongm,

another question...
I think that this code doesn't close internet explorer after the copy. That would cause a new instance of IE to be created and stay open for each time the sub is called. I would really like to create just one instance, and then call it to navigate and copy the text to the dataobject, and then have it quit when the whole process is complete. I am having trouble refering to IE to have it navigate or quit using the code the way you have set it up. I have tried many ways, but can't seem to figure this out. Any help is much appreciated.

Thank you again,

Pat

 

[strongm]

Add

.Quit

after

MrClipBoard.PutInClipboard

 

[pd2004]

Thank you, Sir. I appreciate your patience.