1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

XML Sitemap generator for small sites

Discussion in 'Google Sitemaps' started by sarahk, Jun 5, 2005.

  1. #1
    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
     

    Attached Files:

    sarahk, Jun 5, 2005 IP
    stephenmunday and Will.Spencer like this.
  2. Will.Spencer

    Will.Spencer NetBuilder

    Messages:
    14,789
    Likes Received:
    1,040
    Best Answers:
    0
    Trophy Points:
    375
    #2
    Wow! Extra cool! XENU rocks and the Google script doesn't. :)
     
    Will.Spencer, Jun 5, 2005 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,628
    Likes Received:
    4,486
    Best Answers:
    123
    Trophy Points:
    665
    #3
    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
    Code (markup):
    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
     

    Attached Files:

    sarahk, Jun 5, 2005 IP
  4. marktse

    marktse Peon

    Messages:
    35
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks for the info. Trying it for my site now.
    Mark
     
    marktse, Jun 5, 2005 IP
  5. stephenmunday

    stephenmunday Peon

    Messages:
    393
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Great tool!

    Cheers
     
    stephenmunday, Jun 5, 2005 IP
  6. passat

    passat Peon

    Messages:
    49
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    cool. thanks!
     
    passat, Jun 6, 2005 IP
  7. jumble

    jumble Peon

    Messages:
    142
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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>[url]http://www.domain.net/forums/index.php?showtopic=[/url] 
    Code (markup):
    (repeating in every row)

    Column 2 contains -

    1
    2
    3
    etc...
    
    Code (markup):
    (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>
    Code (markup):
    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>
    Code (markup):
    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>
    [COLOR=Blue]<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>[/COLOR][COLOR=Red]<url>[/COLOR]
    </urlset>
    Code (markup):
    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.
     
    jumble, Jun 6, 2005 IP
  8. Canadianbacon

    Canadianbacon Well-Known Member

    Messages:
    1,231
    Likes Received:
    76
    Best Answers:
    0
    Trophy Points:
    173
    Articles:
    1
    #8
    Very sweet
     
    Canadianbacon, Jun 6, 2005 IP
  9. crumpelfungus

    crumpelfungus Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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
     
    crumpelfungus, Jun 13, 2005 IP
  10. mizt

    mizt Active Member

    Messages:
    772
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    70
    #10
    Umm don't use this on big forums. Its a nice bandwidth killer. Just used a 1 gb in like 2 mins.
     
    mizt, Jun 13, 2005 IP
  11. Blue

    Blue Peon

    Messages:
    65
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Nice tool thanks!
     
    Blue, Jun 13, 2005 IP
  12. wirnik

    wirnik Peon

    Messages:
    968
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Thank you for sharing, good tool.
     
    wirnik, Oct 29, 2007 IP
  13. trichnosis

    trichnosis Prominent Member

    Messages:
    13,785
    Likes Received:
    333
    Best Answers:
    0
    Trophy Points:
    300
    #13
    i was using xmenu to check my broken links until now.

    it's great tip to use:)
     
    trichnosis, Oct 29, 2007 IP
  14. Zanswer

    Zanswer Peon

    Messages:
    264
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #14
    This is a two-year old thread that got bumped up today. but the technique stills works for smaller sites.
     
    Zanswer, Oct 30, 2007 IP
  15. Personaltrainer

    Personaltrainer Peon

    Messages:
    198
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #15
    Thanks for sharing this. Great tool.
     
    Personaltrainer, Oct 30, 2007 IP
  16. baybossplaya

    baybossplaya Active Member

    Messages:
    597
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    58
    #16
    very useful
     
    baybossplaya, Dec 21, 2007 IP
  17. Gallito

    Gallito Peon

    Messages:
    1,939
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    0
    #17
    This thread is ancient but if OP is still around thanks, my site has 9 pages and I still need to make a sitemap =)
     
    Gallito, Dec 21, 2007 IP
    sarahk likes this.
  18. enous

    enous Well-Known Member

    Messages:
    1,500
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    158
    #18
    Ya,good tools...
     
    enous, Dec 21, 2007 IP
  19. britishguy

    britishguy Prominent Member

    Messages:
    7,949
    Likes Received:
    892
    Best Answers:
    0
    Trophy Points:
    360
    #19
    Hi Mods

    Please help quickly
    Thanks
    Best Regards
    Pete
    britishguy
     
    britishguy, Jan 21, 2008 IP
  20. MBrain

    MBrain Member

    Messages:
    24
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #20
    Thanks.. nice tool
     
    MBrain, Feb 7, 2008 IP