XML Sitemap generator for small sites

[sarahk]

When I first started creating sites I used a tool called Xenu to verify that my site indexed ok and that all was well.

I still use it when I'm trying to find my way around the sites of people I'm considering link exchanges with. Sometimes everything is ok but I'm darned if I can find the navigation path.

Anyway, I have a small site which isn't updated so often (despite how often I growl at my husband who is responsible for the content) so I don't want to write elaborate queries to trawl through the site.

So, out comes Xenu to crawl the site and generate the site map. Xenu already does this in part and hopefully Tilman will extend it to include Google's sitemap.

In the meantime this spreadsheet is prepopulated with some of the output from "Export to TAB separated file". Then just run the macro called "CreateGoogleSiteMap" and you'll find in the same location a file called sitemap.xml. It's in the zipped attachment.

I'm no expert in Excel's version of VBA but it's fast and while I am sure it could be better written it'll do me. Let me know if you've found it useful.

Sarah

[Will.Spencer]

Wow! Extra cool! XENU rocks and the Google script doesn't.

[sarahk]

small change to one of the subs so that it can handle parameters

Sub writeLink(url As String, fDate As String)
	url = Replace(url, "&", "&")
	Print #1, "<url>"
	Print #1, "<loc>" + url + "</loc>"
	Print #1, "<lastmod>" + fDate + "</lastmod>"
	Print #1, "<changefreq>Daily</changefreq>"
	Print #1, "<priority>0.8</priority>"
	Print #1, "</url>"
End Sub

Bear in mind that Xenu doesn't obey robots.txt so it will capture local pages you may not want in your sitemap. The best way to handle this is to set up the "don't index" part of xenu which remembers the pages you want excluded. Easier than changing the excel file or the xml file each time

Sarah

[marktse]

Thanks for the info. Trying it for my site now.
Mark

[stephenmunday]

Great tool!

Cheers

[passat]

cool. thanks!

[jumble]

Hi sarah,

Where is the sitemap saved once the macro is run? I could not find it.

I have made a sitemap using excel also but I am no programmer. So I used a less elegant method. I will describe it here if anyone is interested. This method is useful if you have a forum (in my case I use Invision Power Board) and the topics have the following structure -

http://www.domain.net/forums/index.php?showtopic=1
http://www.domain.net/forums/index.php?showtopic=2
http://www.domain.net/forums/index.php?showtopic=3
etc...

To create the hundreds of required url's in a sitemap format, I created 3 columns in excel.

Column 1 contains

<loc>http://www.domain.net/forums/index.php?showtopic=

(repeating in every row)

Column 2 contains -

1
2
3
etc...

(the numbers incrementally increasing on each line - you achieve this by putting in a 1, 2..then selecting them and dragging the controller in the bottom right corner)

Column 3 contains

</loc></url><url>

Then in column 4 I combine these using the "concatenate" function. The result will look as follows -

<loc>http://www.domain.net/forums/index.php?showtopic=1</loc></url><url>

You can then cut and paste them into a file as follows -

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">
<url>
<loc>http://www.domain.net/forums/index.php?showtopic=1</loc></url><url>
<loc>http://www.domain.net/forums/index.php?showtopic=2</loc></url><url>
<loc>http://www.domain.net/forums/index.php?showtopic=3</loc></url><url>
</urlset>

Don't forget to delete the red <url> (see above) entry from the end of the last url line!

Now you have a valid XML file to submit.

Cheers

EDIT: I have provided some additional explanation here.

[Canadianbacon]

Very sweet

[crumpelfungus]

Love it! Great work.

So, basically we:
1) Download your spreadsheet
2) Add the new code (posted in a subsequent thread) to the Macro VBA
3) Get Xenu and run the report, save it as a tab-delimited file
4) Import the Xenu report into MS Excel (either into a new workbook or add a worksheet to your "template" or replace your information altogether)
5) Ta-dah! Done.

By default, the sitemap.xml file gets saved to MY DOCUMENTS.

If you want the sitemap.xml file to be saved to the same directory in which the current MS Excel file is located, change the top of Sub OpenIt() as follows:


Sub OpenIt(cBase As String)
Dim fSitemapPath As String
'GET THE CURRENT FILE'S LOCATION
fSitemapPath = ActiveWorkbook.Path
Dim fSitemap As String
'Now create the tags file.
fSitemap = fSitemapPath & "/sitemap.xml"


The attached MS Excel spreadsheet is a mildly revised version of Sarah's original, including all discussed and disclosed changes up to this point.

Thanks for your pioneering in this direction, Sarah!


PS: Hmmm...the upload feature seems to be temporarily down. Instead, feel free to download the revised MS Excel file with Sarah's macro from:


http://www.humdinger-media.com/sitemap/rev_pagemap.zip

[mizt]

Umm don't use this on big forums. Its a nice bandwidth killer. Just used a 1 gb in like 2 mins.

[Blue]

Nice tool thanks!

[wirnik]

Thank you for sharing, good tool.

[trichnosis]

i was using xmenu to check my broken links until now.

it's great tip to use

[Zanswer]

This is a two-year old thread that got bumped up today. but the technique stills works for smaller sites.

[Personaltrainer]

Thanks for sharing this. Great tool.

[baybossplaya]

very useful

[Gallito]

This thread is ancient but if OP is still around thanks, my site has 9 pages and I still need to make a sitemap =)

[enous]

Ya,good tools...

[britishguy]

Hi Mods

Please help quickly
Thanks
Best Regards
Pete
britishguy

[MBrain]

Thanks.. nice tool