Get HTTP MetaData into Excel

Just to say, I know there are lots of programmes that can solve this question, such as http://www.link-assistant.com but what i am looking for is the soltuion in XLS format....

Any gurus out there?

I threw together a quick UDF that you could use to insert the Meta Description into a Excel Spreadsheet cell. For exmple if the URL is in A1, put this formula in B1 after you've inserted the code below into a standard module in the workbook, and created a reference to the HTML library for the project.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi Shred Dude

Have done this below

Originally Posted by ShredDude

put this formula in B1 after you've inserted the code below into a standard module in the workbook,

What is this? "and created a reference to the HTML library for the project."


After I state the getMetaDescription() there is an error in debug at

Please Login or Register  to view this content.

Thanks for offering this up, I'm new to Excel/VBA, and have kinda caught the bug!

BTW, have Office 2010

Cheers

XLS_Gunda

In the Visual Basic Editor (VBE), go tot he Tools Menu, then References. Scroll down in the list that appears looking for Microsoft HTMLObject Library. Check that box and then click OK. That establishes a Reference to the HTML object library.

Your profile shows you're using Excel 2003, you might want to update that if you're really using 2010.

Hi

Have done as you suggest, and attached the XLS here....

Have i got it all set up correctly, as you say, because it is not returning the Meta as thought to do...

Feels like "we" are almost there....


thoughts?

The file you posted contains no macro code. If it did it would have the .xlsm file extension.

Attached is a version containing the code and the appropriate reference.

There is no error checking in the UDF so if a page has no Meta Description tag within it's source, you'll get a #VALUE returned in the spreadsheet. Add whatever error checking you feel is appropriate for you needs.

Looks like the createDocumentFromUrl likes to have "http://" at the front of the URL.

Added a line of code to handle that...

Please Login or Register  to view this content.

No, I don't think that'll do it. getelementsbytagname returns an IHTMLElementCollection. You'd need to then reference a particular element within that Collection.

If you're certain there'd only be one tag with the name "Title", you might try something like:

Please Login or Register  to view this content.

Or, abandon the HTML Document method and do an xmlhttp request then string parse the responsetext for the Title Tag's contents.

Please Login or Register  to view this content.

I didn't use this method on the Description because of the way the Description Tags were formatted. I found it easier to use the DOM to extract the "Content" of a node.

The Item property is looking for the NAME attribute of the element. That's why it worked on the Meta Tag because within the Meta Tags each one was named, and there was one named "Description".

OnClick is not a Name, rather a property of an Anchor element in this case.

So, if it's the OnClick property that is your unique identifier you want to serach on, I'd suggest grabbing all the anchor elements into a collection and then looping through them until you find the one you want.

Try something like this:

Please Login or Register  to view this content.

That is if you want to use the HTML method. Depending on the site, this could be slow if there are many Anchor Elements to search through. You may want to consider using the XMLHTTP approach, and making yourself a little parser routine to grab the href that follows the OnClick you're looking for.

Shred....Promise this is the last pass!

So, have tried the function getTracking() method, sadly to no avail.

Also had a look at entering other anchors/properties to check that too....

Tomorrow (Friday) is my last day on this blasted two-week contract (recession = ), this help you give will help me leave a fellow Scot with a bit of code that makes his impossible job infinitely easier....as for the nazi boss...she be none the wiser either way!

Your tutorials have been brilliant, i have learned alot about XLS. V Many thanks.

I've attached the XLS....... in for a penny and all that!

VBA doesn't like it when you name a module the same as a function. That was the cause of the #NAME error.

After that, I fixed the code to find the instance where you have just the part you're interested in in the OnClick property.

So, how much money am I helping you make?

Originally Posted by ShredDude

So, how much money am I helping you make?

The contract is paying well, but it is not about the Excel functions.

It was more for my own interest that I started trying to work out how to do these functions (and failing miserably!).

To be honest with you mate, this contract has been so so so sh*t that had it not been for these excel functions and trying to solve a problem that no one asked to be solved, while learning and interacting with the likes of yourself.....well it would have been even more sh*t.

PM your PayPal details and I'll buy you a few beers....as a personal thank you.

I know that this thread is ancient but I can't get this to work for me. I've built a Browser History processor for my job (which seems to be mostly checking up on employee browing habits this week) and going through history logs for IE, Firefox, and Chrome is a pain.

My spreadsheet takes data from 4 sheets of data (IE, Chrome, Firefox, and Safari - added it because I have a history viewer for it) then weeds out any allowed sites (work or research related) then spits out the data on a final sheet that also is color highlighted for breaks and lunch browsing. Leaving the manager the end data to look over for possible discipline.

I was using some code that used IE to get the title but if the page pops a dialog warning (or a download dialog) it causes me to have to use interaction.

I plugged this code into my macro (not using it as a formula based approach as I have thousands of URLs to process) and I get a method "open" of the object "IXMLHTTP" failed.

Any ideas?

When i run the code i am getting an error
"Object Variable or With Block Variable not set"

Can you please resolve this!!!

Please Login or Register  to view this content.