Macro for website copy and paste into excel...

[MTHassan]

Is it possible to copy from the website and paste into excel table by using macro code?
just like what I am doing manually copy from website and paste into excel .... plz advise if its possible...
web query is not working for this website... but manually copy and paste is working...
So, I need a macro code which can do the job...

http://www.biasl.net/pages/CompanyFundamental.aspx

Thanks

 

[Ruddles]

This is a duplicate thread: http://www.mrexcel.com/forum/showthread.php?t=507225

I've seen this sort of embedded report before and I've never managed to scrape the data into Excel from it yet. I think the best you can hope for is to have two macros: one which starts IE and navigates to the page; then the user has to the table into the clipboard; and finally a second macro processes the data in the clipboard and places it into the worksheet.

 

[MTHassan]

Than answer something...instead of the thread behind back...

 

[Norie]

Is it the company fundamentals table that the page that link goes to you want the data from?

If it is then it shouldn't be a problem to get just that.

Getting other data might be though.

For example there's some sort of page selection/goto button at the bottom.

Are there other pages?

 

[MTHassan]

Yes I just want the data into table not in one column... (if I copy and paste manually the works fine but I need a macro for just copy the webpage data and paste it into excel sheet...that will be more helpful)...

 

[Norie]

Try this.

Option Explicit
 
Sub MrExcel13Nov2010()
Dim wsNew As Worksheet
Dim rng As Range
Dim IE As Object
Dim doc As Object
Dim tbls As Object
Dim divContent As Object
Dim tbData As Object
Dim rw As Object
Dim col As Object
Dim cl As Object
Dim strBaseURL As String
 
    strBaseURL = "http://www.biasl.net/pages/CompanyFundamental.aspx"
 
    Set wsNew = Worksheets.Add
 
    Set rng = wsNew.Range("A1")
 
    Set IE = CreateObject("InternetExplorer.Application")
 
    IE.navigate strBaseURL
 
    Do While IE.Busy: DoEvents: Loop
 
    Do While IE.ReadyState <> 4: DoEvents: Loop
 
    IE.Visible = True
 
    Set doc = IE.Document
 
    Do While doc.ReadyState <> "complete": DoEvents: Loop
 
    Set divContent = doc.getElementById("myGrid1_bodyDiv")
 
    Set tbls = divContent.getelementsbyTagName("TABLE")
 
    Set tbData = tbls(0)
 
    For Each rw In tbData.Rows
 
        For Each cl In rw.Cells
            rng.Value = cl.innertext
            Set rng = rng.Offset(, 1)
        Next cl
 
        Set rng = wsNew.Cells(rng.Row + 1, 1)
 
    Next rw
 
    IE.Quit
 
End Sub

 

[clinton holder]

Moderator edited

 

[MTHassan]

its not working....shows an error message...

 

[UHsoccer]

Norie,

I try to execute the code it displayed the "Company Fundamentals" web page but I received an error on

"For Each rw In tbData.Rows" (object variable or with block variable not set)

tbData is nothing

 

[Norie]

The code works for me, and I didn't actually say it was perfect.

Doing this sort of things can be affected by a whole lot of things.

One of the main things being connection speeds but there's plenty of others.

Not all of them are on the 'client' side either.

This page is linking back to a server where all the data is stored.

One of the first thing's it does as far as I can tell is send a request to that server to retrieve that data - that's why you get the Please waiting.... message.

It's quite hard to deal with these sort of things to write code that will work every time for everybody.

What I've posted is the basics if you like of the code you would need to do this sort of thing.

Obviously it can be improved, refined and if anybody has any ideas how to do that I'd be happy to hear them.

I actually thought I'd dealt with the 'waiting' problem using techniques I've used elsewhere for similar pages but looks like I didn't.

I've got a few other ideas but I've still not had an answer to the one of the original question to the OP - does that page have all the data.

Also if you are getting errors please say what they are, like UHSoccer(thank you) has.

I'll go away and see if I can work out a fix but it might not be too pretty.

clinton holder

If what you want to do involves logging in with a username/password etc then I'm afraid I can't really help.

Don't think it's quite right to post code for that sort of thing.

Anyway, if you look at the code I posted one of the first things it does is create a reference to the document in IE.

That's basically the entire page, so in theory if you've got that you've got access to everything on the page.

Actually extracting data/whatever from it is a different matter, I'm pretty sure you don't want the entire page.

If you did all you would need would be the innerHTML or innerText property of the document.

You'd need to parse the HTML or Text to get data.