+ Reply to Thread
Results 1 to 1 of 1

Macro to extract / Scrape specific part of website table & search windows directories

  1. #1
    Registered User
    Join Date
    04-14-2011
    Location
    Bay Area, CA
    MS-Off Ver
    Excel 2007
    Posts
    32

    Macro to extract / Scrape specific part of website table & search windows directories

    Hi,

    So I've seen posts on "scraping" or extracting website data, and even the Microsoft help topics has a nifty description on how to use a web query to get data from a website. And that's all well & good, and everything works like it should. Except my issue is a tad different (read: COMPLICATED), and I can't seem to find the answer to the specific issues I'm facing.

    ISSUE #1: First, I only need to extract certain columns of the web table, not the whole table. But for whatever reason, when I go through the "get data from web" option as I'm recording the macro, and the little arrow-thingies come up to allow you to select what data you wanna grab, it's only showing me the option to get the whole table, not individual columns within the table - so, I think what I need to do is use some sort of more robust commands to actually go into the webpage, figure out the code to where the data resides that I'm interested in, and copy that - any advice or posts which can help with this? Either that, or just continue to get the whole table from the website, then use the macro to parse out the information I don't want & only present the columns I'm interested in??? I dunno, that's why I'm coming to you gurus...

    ISSUE #2: Second, once that is done, one of the columns (let's just say Column "B") has text in it that could be the name of a file that might exist in a directory on the computer. So, I'd like to have Excel grab this text & then use as a keyword to search a Windows explorer window, sort of like copying that text & pasting it into the search bar of the explorer window to see if it matches any of the files in there. I don't think Excel needs to specifically "open" the explorer window to be seen by the user, this can be done invisibly in the background - but if it finds a match (or matches), it should pop up a message saying something like "Search found these files which might be what you are looking for - which one would you like to open?" and then there will be a user action to click on one of the files to open it (to see if it's the one they are interested in).

    As an example, let's say the files reside in "C:\Libraries\Documents\Downloaded_Files" - so if you were to manually go there in Windows explorer, you could then copy the filename you were interested in from Column B & paste it into the Search bar on the Right-Hand side to see if it exists. If it exists, then it highlights it for you, and you can double-click on it to open it. I'd like for all of the copying, pasting, and searching to be done automatically, and give the user the option to open it.

    ISSUE #3: The text in that column might not be exactly the text of the filename. For instance, the text might be "ABC-123.xls" and because someone mis-named the actual file, it could be "ABC-123A.xls" - so I would like to be able to take the text and then add in some wildcards as well. That way I can ensure that I'm looking for all permutations of the filename.

    ISSUE #4: Since there can be 1 - N rows of data, and each row in Column "B" represents a different filename that needs to be searched, what I'm envisioning is a macro that goes to the webpage, grabs specific columns of data, and then presents a message to the user which says "Click the specific cell in Column "B" to invoke a search on that filename." I think that would give more freedom than just "looping" over Rows 1 - N (though, I also am aware this might be more difficult to do in practice).

    Anyway, I know that this is a lofty goal, and the issues I'm facing are quite complex, and I'm not expecting someone to reply with a handy-dandy macro that does everything all at once - but if multiple folks who are proficient in the different parts of each of the issues could chime in & help peck away at this 1 issue at a time, then that would help a LOT. Even something that points me to a similar thread where I could see how they did it would help.

    The thing is, I sort of told the boss that I could automate the current process of searching for these files. Right now, it's woefully manual & labor & time intensive, and if we could at least automate some of it, that would help tremendously!!

    I know many of you gurus out there can get me on the right track, so for that, I'm thanking you in advance!!
    Last edited by rbrookov; 10-16-2013 at 10:59 AM. Reason: clarify issue

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to extract a specific data from a website using macros or vba
    By pari9485 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-08-2013, 10:42 PM
  2. Macro to extract table data from website into my excel file
    By garbisrobert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2012, 02:38 PM
  3. [SOLVED] Pull Specific Data from Website (Screen Scrape)
    By CHRISEWRIGHT20 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-11-2012, 11:03 AM
  4. VBA to scrape data from website
    By jdub22cb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2012, 04:00 PM
  5. Link data in cells to a search on a specific website
    By gideonjav in forum Excel General
    Replies: 1
    Last Post: 10-16-2009, 12:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1