make html files from excel cells

Microsoft / Excel
November 9, 2010 at 16:06:14
Specs: Windows Vista, 2gb
Hi,

I have an excel file which contains about 2000 rows. One column contains text that can be as big as 5000 chars. I need to create separate html files that contain this text - one per row. So that should give me 2000 html files. I want to name the html files using another column in the spreadsheet.

Col A Col B
Fred FredText
James JamesText
Mary MaryText

In this example I want to create 3 files called Fred.html, James.html and Mary.html with each file containing the Col B text.

Could anyone help me with a suitable macro?

Much obliged.

John


See More: make html files from excel cells

Report •

#1
November 10, 2010 at 05:13:33
This code will, line by line:

1 - Copy the data in Column B to Sheet 2
2 - Copy Sheet 2 to a new workbook
3 - Save the new workbook as an html file
4 - Use the value in Column A as the file name
5 - Close the html file

I suggest that you try this in a backup copy of your workbook in case things go terribly wrong. macro cannot be undone.

Notes:

1 - You'll need to edit the directory path in order to save the files where you want to save them to.
2 - I added a line to save just the first 3 files so that if it's wrong, you won't have 2000 incorrect files. To have it work for all lines in the spreadsheet, comment out (or delete) the lastRw = 3 line and uncomment the line that determines the actual length of the spreadsheet.

Option Explicit
Sub MakeHtmFiles()
Dim lastRw, rw As Integer
Dim fName As String
'Stop screen from flickering
 Application.ScreenUpdating = False
'Note: Temp line to save only 3 files
'Comment (or delete) when finished testing
  lastRw = 3
'Determine last row with data in Column A
'Remove comment symbol to use this line
 'lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through data
  For rw = 1 To lastRw
'Save value in Column A to use as Filename
    fName = Sheets(1).Cells(rw, 1)
'Copy Column B data to Sheet 2
      Sheets(1).Cells(rw, 2).Copy _
        Destination:=Sheets(2).Cells(1, 1)
'Copy Sheet 2 to a new workbook
          Sheets(2).Copy
'Save new workbook as html file
          ActiveWorkbook.SaveAs Filename:= _
             "C:\Documents and Settings\Dave\My Documents\" _
             & fName & ".html", FileFormat:=xlHtml, _
             ReadOnlyRecommended:=False, CreateBackup:=False
'Close the new workbook
          ActiveWindow.Close
   Next
  Application.ScreenUpdating = True
 End Sub


Report •

#2
November 10, 2010 at 05:58:07
Thanks so much for your reply. I've just tried it and it copied just one row to Sheet 2 but gave me me the 3 html files ok also but these files did not contain the full text in column B. Any ideas?

Report •

#3
November 10, 2010 at 06:13:52
re: it copied just one row to Sheet 2

Actually, it copied every row, it just did it 1 row at a time. If I understand you correctly, we are trying to create 2000 files, each containing just a single line of text.

You are only seeing 1 row as being copied (probably the last one) because the code is copying each value from Column B into the same cell (Sheet2!A1) then saving the file and then overwriting Sheet2!A1 with the data from the next row.

re: these files did not contain the full text in column B.

How many characters did it copy?

The Column B cells contain actual text, not formulas that make it look like text, right?

I tested it with short strings, not 5000 characters. I'll need to test that further, but I don't have time right now.


Report •

Related Solutions

#4
November 10, 2010 at 06:23:45
I don't have time to fix it right now but the problem is (I forgot) that when you copy a sheet it only copies the first 255 characters in the cells. You'll get a warning if you try it manually, but not when you do it with code.

We'll need to create a new workbook, copy the text (not the sheet) to that new workbook and then save it as html.

I'll work on that when I get a chance.


Report •

#5
November 10, 2010 at 10:42:27
Try this:

Option Explicit
Sub MakeHtmFiles()
Dim lastRw, rw As Integer
Dim curName, newName As String
'Stop screen from flickering
 Application.ScreenUpdating = False
'Save Current Filname
 curName = ThisWorkbook.Name
'Note: Temp line to save only 3 files
'Comment (or delete) when finished testing
  lastRw = 3
'Determine last row with data in Column A
'Remove comment symbol to use this line
 'lastRw = Workbooks(curName).Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through data
  For rw = 1 To lastRw
'Save value in Column A to use as Filename
    newName = Workbooks(curName).Sheets(1).Cells(rw, 1)
'Add new workbook
     Workbooks.Add
'Copy Column B data to new workbook
      Workbooks(curName).Sheets(1).Cells(rw, 2).Copy _
        Destination:=ActiveWorkbook.Sheets(1).Cells(1, 1)
'Save new workbook as html file
        ActiveWorkbook.SaveAs Filename:= _
             "P:\" _
             & newName & ".html", FileFormat:=xlHtml, _
             ReadOnlyRecommended:=False, CreateBackup:=False
'Close the new workbook
          ActiveWindow.Close
   Next
  Application.ScreenUpdating = True
 End Sub

Please note: Before posting Data or Code, read this How-To.


Report •

Ask Question