Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro or some method to grab data and paste

    Greetings,

    Third times a charm! Since I struck out in the Excel fourm, and my post was locked in the IE fourm, I will try here.

    Basically, I need to open a Intranet site here at work, run thru the pick lists, grab the resulting data and paste into Excel. I have tried the inport data (web) in Excel, but for some reason it will not grab the actual data that appears. If I manually select the information on the web page I am able to paste it where I need it. However I was wanting to automate the steps.

    Any thoughts?

    Thanks,
    Brad

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro or some method to grab data and paste

    Hi Brad

    I was watching the thread in Excel with interest as it did appear at first to be a simple automation using the HTML functionality. Can I go through a few things with you first just to get the flavourt of your intranet and the various quirks it may have.

    1) In <post:=668,482>post 668,482</post:> you mentioned java. Just so I am clear is it Java or javascript?
    2) Is it tabular data you are looking to collate?
    3) If not #2 can you describe what you are trying to download
    4) Don't worry about the lock in IE, Mods and Admin move posts to where they think you will get a better response and this one does look like a cross platform, meaty question <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro or some method to grab data and paste

    ....and further to Jeff's idea, you appear to be using Excel 2003 , and may want to look at Microsoft Office 2003 Web Services Toolkit
    Jerry

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro or some method to grab data and paste

    <P ID="edit" class=small>(Edited by jscher2000 on 01-Oct-07 13:06. )</P>If you are using IE for your development and testing, you might benefit from Microsoft's (unsupported) developer toolbar. It has a "View DOM" option that often lets you inspect the page and drill down into the various layers of nested HTML to help figure out how to access the data. See Download details: Internet Explorer Developer Toolbar.

    Added: I just realized I had a year-old version of the toolbar. The new one does not use the term "View DOM." Instead, after you add its button to the toolbar, just clicking the button will display the DOM tree.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or some method to grab data and paste

    Whew...

    Lots to chew on. In looking at the source data for the page after it is created, it appears that they are using javascript to derive the data. It basically displays in Rows and Columns. The info in the cell is hyperlinked, but I do not see as a problem. I think if it is approached as a method to "single" click something, and run the various commands to initiate IE, plugin the URL, the cycle through the various picklists, the grab the data and
    1) open an excel workbook and paste, or
    2) start this via excel, and paste into a worksheet.

    This is a virgin area for me, as I just have the Idea, and am looking for Ideas/thoughts from the collective. It could very well be overly complicated and as such not worth the effort.

    Thanks,
    Brad

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro or some method to grab data and paste

    Hi Brad

    I have a colleague who is Football (soccer) crazy and an avid fan of a certain team and wanted tabular data WLDP type data straight from their web site and I simply did it with the method first mentioned in the original Excel question. Just so that we are both at the same level and we can work up together, can I go right back to basics ( my apologies but I am not sure your expertise level so we can go from ground up)

    In the Excel workbook, can I assume that when you have created the web query you have selected the tabular data that you require. In Excel, this is by ticking the yellow arrow so that it turns to a tick with a green background (see below, red and circled) and then pressed Import.

    I am double checking as it sounds like the data is being written to page in a <table> and if so should have this option ( I write this with reservation as you never know with some company we developers <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

    If this is not working can you add a screen dump of what you actually see as it might help us diagnose it a bit better <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or some method to grab data and paste

    Can I just ask a stupid question - if this is an intranet, i.e. the data you require is provided by someone else in your organisation - have you contacted them directly to ask if they can provide the data to you in a more usable format? Presumably that would be much quicker (although less fun/interesting) than trying to find a technical solution.

    If all else fails, another possible approach is to use SendKeys in Excel...
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or some method to grab data and paste

    Hi All...

    To answer the question, yes I clicked the arrow and it did turn green. I will have to "mock up" the information, as it is confidential to our company. The odd thing to me is that when I select the information while on the web page, and then paste into excel,,, no problems. Pastes as I would expect. However when I use the Data Import, etc... And select all of the arrows, only the header of the page comes in.

    Please stand by as I mock up the screen...


    Thanks,
    Brad

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or some method to grab data and paste

    While looking at the data, A thought occurred. The Data is dynamically created. Meaning, it is querying the data, and displaying with links to the informational screens associated. At the bottom of IE, when hovering over the link I get this:

    java script:win('kr1p2.php?fitemidx_x=24&ftaridx_in=879 2')

    Could this be the reason I am unable to automate??


    I attached the image, did not know how to show here....

    Thanks,
    Brad
    Attached Images Attached Images
    • File Type: jpg 1.jpg (24.3 KB, 0 views)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro or some method to grab data and paste

    You say that the yellow arrow turned into a green tick mark when you clicked it. What happened when you clicked the Import button?

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or some method to grab data and paste

    Upon clicking the tick mark, and beginning the import, it said it was importing. Once it completed all I got was the "non" table information that was on the site. All of the "table" information did not import. I checked to see if perhaps the information was hidden by means of color, but not the issue. There simply was nothing there.

    I suppose it could be the way the information is being created and displayed. Going to the admin for the site will not accomplish anything, as the info is dynamically generated as an "on demand" situation. This is perplexing. I have changed the options to see if I get anything different, and it does not change.

    Brad

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro or some method to grab data and paste

    I think Waggers meant something like this: apparently, there is code that gathers data depending on the criteria that you provide, and presents it in a browser window. It should be possible for the person who wrote that code to make the data available in another way that is easier to use, for example as a .csv file that can be downloaded to your PC. That would be much more efficient than what you're trying to do now. (The downloadable file would be generated on demand, so you'd still get the customized data you need).

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro or some method to grab data and paste

    > when hovering over the link I get this:

    > <code>java script:win('kr1p2.php?fitemidx_x=24&ftaridx_in=879 2')</code>

    > Could this be the reason I am unable to automate??

    If you don't need to click the link to see the data, then Excel should replicate the exact data and links into the workbook.

    Maybe there is something unusual about the structure of the tables, for example, if they are heavily nested.

Posting Permissions

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