Sitemap generator in Excel VBA


Here is my sitemap generator written in Excel VBA. It generates a file list and then parses the page title from the text of the file. Then using the following formula

=CONCATENATE("<br/><a href='",A1,"'>",IF(B1="",A1,B1),"</a>")

it generates an HTML statement ready for inclusion in my sitemap.html. Here is the Mac version in Excel for Mac 2011.

Here is my sitemap generated using this Excel VBA sitemap generator.

Google's Webmaster Tools says that the vast majority of indexed pages are as a result of spidering websites. The effect of this type of sitemap is that it links every page on the website even the ones that the spider does not see because of the way they are linked. 


Sub sbSiteMap()
 Dim sPath As String
 Dim sFilename1 As String
 Dim sFilename2 As String
 Dim sCell As String
 Dim iCount As Integer
 Dim iFileNum As Integer
 Dim sDataLine As String
 iCount = 1
 iFileNum = FreeFile()
 sCell = "A" & iCount
 sPath = "C:\Users\Dave\Google Drive\mywebsite"
 sFilename1 = Dir(sPath & "\*.html")
  Do While sFilename1 NE ""
    Range(sCell).Select
    ActiveCell.FormulaR1C1 = sFilename1
    sFilename2 = sPath & "\" & sFilename1
    Open sFilename2 For Input As #iFileNum
    While Not EOF(iFileNum)
    Line Input #iFileNum, sDataLine  ' read in data 1 line at a time
    If InStr(1, sDataLine, "<title>") > 0 Then
        sCell = "B" & iCount
        Range(sCell).Select
        ActiveCell.FormulaR1C1 = _
         Mid(sDataLine, InStr(1, sDataLine, "<title>") + 7, _
         InStr(1, sDataLine, "</title>") - InStr(1, sDataLine, "<title>") - 7)
    End If
    Wend
    Close #iFileNum
    iCount = iCount + 1
    sCell = "A" & iCount
    sFilename1 = Dir()
  Loop
End Sub


Tags - Excel 2010, Excel 2007, Excel 2002, sitemap, website, HTML, generator, VBA, code, Microsoft, Visual BASIC for Applications, pages, indexing, spider, CONCATENATE, webmaster, tools, title, links ...


.