Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Macro for website copy and paste into excel...

This is a discussion on Macro for website copy and paste into excel... within the Excel Questions forums, part of the Question Forums category; Is it possible to copy from the website and paste into excel table by using macro code? just like what ...

  1. #1
    New Member MTHassan's Avatar
    Join Date
    Nov 2010
    Posts
    22

    Default Macro for website copy and paste into excel...

    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

  2. #2
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,473

    Default Re: Macro for website copy and paste into excel...

    This is a duplicate thread: Import data into excel...

    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.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  3. #3
    New Member MTHassan's Avatar
    Join Date
    Nov 2010
    Posts
    22

    Default Re: Macro for website copy and paste into excel...

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

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Macro for website copy and paste into excel...

    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?
    If posting code please use code tags.

  5. #5
    New Member MTHassan's Avatar
    Join Date
    Nov 2010
    Posts
    22

    Default Re: Macro for website copy and paste into excel...

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

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Macro for website copy and paste into excel...

    Try this.
    Code:
    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
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Jan 2006
    Location
    Minneapolis, MN USA
    Posts
    7

    Default Re: Macro for website copy and paste into excel...

    Moderator edited
    Last edited by Jon von der Heyden; Apr 13th, 2011 at 03:00 AM.

  8. #8
    New Member MTHassan's Avatar
    Join Date
    Nov 2010
    Posts
    22

    Unhappy Re: Macro for website copy and paste into excel...

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

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    929

    Default Re: Macro for website copy and paste into excel...

    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
    Excel 2003 and 2010 on 64bit Windows-7

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Macro for website copy and paste into excel...

    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.
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com