Excel example to create “hundreds” of HTML files based on list of filenames in Column A and skeletal lines of code in Column C.

Make 100s of HTML files based on skeletal code

Location: http://www.mvps.org/dmcritchie/excel/make100s.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Sample worksheet with names of files, and html skeleton source

This example is capable of generating “hundreds” of HTML files based on list of filenames in Column A and skeletal lines of code placed in Column C.  You can easily use a separate file for the HTML skeletal code.  Below the table you can see the generated output as it would appear in your browser. 

On the right is the generated HTML code found in one of the files, as you would see it in Notepad.  Below that you see example of directory similar to what you would see in the Command Window or any directory listing.
 

 ABC
 1 file001   <html>
 2 file002   <head>
 3 file003   <meta NAME="robots" CONTENT="noindex,nofollow">
 4 file004   </head>
 5 file005   <body>
 6 file006   <ul>
 7    <li>Line 1 This HTML file is in $$FILE$$
 8    <li>Line 2 of file created on $$DATE$$
 9    <li>Line 3 of file created at $$TIME$$
10    <li>Line 4
11    </ul> <! Next line is blank in source>
12   
13    </body>
14    </html>

The table above contains CHAR(160) which is the non-breaking space (&nbsp; in HTML), if you copy and paste it to an Excel sheet, use the TrimALL macro to convert them to spaces and then to TRIM each cell.

The Generated file006.htm appears in browser as follows:
  • Line 1 This HTML file is in c:\temp\Make100s\file006.htm
  • Line 2 of file created on 2005-07-13
  • Line 3 of file created at 08:21:50
  • Line 4
Generated file006.htm 
<html>
<head>
<meta NAME="robots"
 CONTENT="noindex,nofollow">
</head>
<body>
<ul>
<li>Line 1 This HTML file is 
in c:\temp\Make100s\file006.htm 
<li>Line 2 of file created on 2005-07-13
<li>Line 3 of file created at 08:21:50
<li>Line 4
</ul>  <! Next line is blank in source>

</body>
</html>

  
 Directory of C:\temp\make100s
2005-07-13  08:21a  311 file001.htm
2005-07-13  08:21a  311 file002.htm
2005-07-13  08:21a  311 file003.htm
2005-07-13  08:21a  311 file004.htm
2005-07-13  08:21a  311 file005.htm
2005-07-13  08:21a  311 file006.htm

VBA code

Option Explicit
Sub Make100s()
  'David McRitchie, 2005-07-12
  Dim path As String
  Dim filename As String
  path = "c:\temp\Make100s\"
  Dim I As Long, J As Long, R As Long, B As Long
  Dim str1 As String, str2 As String, str3 As String
  R = Cells(Cells.Rows.Count, 1).End(xlUp).Row
  B = Cells(Cells.Rows.Count, 3).End(xlUp).Row
  For I = 1 To R
    filename = path & Trim(Cells(I, 1)) & ".htm"
    Close #1
    '-- will create new file or rewrite over existing file with same name
    Open filename For Output As 1
    For J = 1 To B
       str2 = Cells(J, 3).Text
       '-- Note:  SUBSTITUTE is case sensitive
       str2 = Application.Substitute(str2, "$$FILE$$", filename)
       str2 = Application.Substitute(str2, "$$DATE$$", Format(Date, "yyyy-mm-dd"))
       str2 = Application.Substitute(str2, "$$TIME$$", Format(Time, "hh:mm:ss"))
       Print #1, str2
    Next J
    Close #1
  Next I
  'Show first file in Notepad
  Shell "notepad " & path & Cells(1, 1) & ".htm"
  'Show last file created in web browser (based on ".htm" extension)
  Dim RC As Long
  '-- open in browser based on your SHELL
  RC = Shell("Explorer " & filename, 1)
  '-- specifically open in Firefox -- use F5 to refresh browser view
  RC = Shell("C:\Program Files\Mozilla Firefox\firefox.exe " & filename, 1)
End Sub
This Page is best viewed in a web browser.
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on July 13, 2005. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2005,  F. David McRitchie,  All Rights Reserved