Madiya wrote:

Is it possible to create and display a web page on the fly with VBA?

Define "on the fly". Do you want the HTML to be regenerated every time a
change is made to the spreadsheet? Easily done. Want it to be callable from
the web server, via CGI or whatever? Not so easy. (I'm not even sure if
it's possible.) Want something else? Be more specific.

Web page needs to show the data from a range, possibly with formatting
but not necessary.

This code will create a *simple* HTML file. It doesn't deal with things
like font changes in the middle of a value (e.g. a single word bolded in a
cell) or many other formatting constructs, but it works for me. Mostly.

Sub rangeToHTMLFile(what As Range, fileName As String)
Dim cell As Range, prevRow As Long, fHnd As Long
fHnd = FreeFile
Open fileName For Output As fHnd
Print #fHnd, "<HTML<HEAD<TITLETesting...</TITLE</HEAD"
Print #fHnd, "<BODY<TABLE BORDER=1 ROWS=" & Trim$(what.Rows.Count);
Print #fHnd, " COLS=" & Trim$(what.Columns.Count) & ""
For Each cell In what.Cells
If prevRow < cell.Row Then
Print #fHnd, "<TR"
prevRow = cell.Row
End If
Print #fHnd, "<TD BGCOLOR=";
Print #fHnd, vbColorToHtmlColor(cell.Interior.Color); "";
Print #fHnd, "<FONT FACE="""; cell.Font.Name; """ COLOR=";
Print #fHnd, vbColorToHtmlColor(cell.Font.Color); "";
If cell.Font.Bold Then Print #fHnd, "<B";
If cell.Font.Italic Then Print #fHnd, "<I";
'etc.
'any formatting you want to capture must be manually dealt with
Print #fHnd, cell.Value;
'good form to close any html tags that need it...
If cell.Font.Italic Then Print #fHnd, "</I";
If cell.Font.Bold Then Print #fHnd, "</B";
Print #fHnd, "</FONT"
Next
Print #fHnd, "</TABLE</BODY</HTML"
Close fHnd
End Sub
Function vbColorToHtmlColor(vbc As Long) As String
Dim tmp As Long, outP As String
tmp = vbc And &HFF
outP = IIf(tmp < 10, "0", "") & Hex$(tmp)
tmp = (vbc And &HFF00&) \ 256
outP = outP & IIf(tmp < 10, "0", "") & Hex$(tmp)
tmp = (vbc And &HFF0000) \ 65536
outP = outP & IIf(tmp < 10, "0", "") & Hex$(tmp)
vbColorToHtmlColor = outP
End Function

Call it like this:
rangeToHTMLFile Selection, "C:\test.htm"

Edit as you see fit.

--
I need this! I haven't been involved in any chaos for an hour!

Hi Auric,
Thanks a lot.
I will be more specific.

I am generating different html files based on the data in my excel file.
For each file, I will be applying specific filters in excel, select the resultant visible data and make a html file for the same.
Same process will be repeated with a for...next loop for all different variables.
I have tested your code given and no edits are necessary.
Everything I need is already there.

Once again thanks a lot.
I will be back if need any help.

Regards,
Madiya
On Tuesday, 3 April 2012 05:34:52 UTC+5:30, Auric__ wrote:
Madiya wrote:

Is it possible to create and display a web page on the fly with VBA?

Define "on the fly". Do you want the HTML to be regenerated every time a
change is made to the spreadsheet? Easily done. Want it to be callable from
the web server, via CGI or whatever? Not so easy. (I'm not even sure if
it's possible.) Want something else? Be more specific.

Web page needs to show the data from a range, possibly with formatting
but not necessary.

This code will create a *simple* HTML file. It doesn't deal with things
like font changes in the middle of a value (e.g. a single word bolded in a
cell) or many other formatting constructs, but it works for me. Mostly.

Sub rangeToHTMLFile(what As Range, fileName As String)
Dim cell As Range, prevRow As Long, fHnd As Long
fHnd = FreeFile
Open fileName For Output As fHnd
Print #fHnd, "<HTML<HEAD<TITLETesting...</TITLE</HEAD"
Print #fHnd, "<BODY<TABLE BORDER=1 ROWS=" & Trim$(what.Rows.Count);
Print #fHnd, " COLS=" & Trim$(what.Columns.Count) & ""
For Each cell In what.Cells
If prevRow < cell.Row Then
Print #fHnd, "<TR"
prevRow = cell.Row
End If
Print #fHnd, "<TD BGCOLOR=";
Print #fHnd, vbColorToHtmlColor(cell.Interior.Color); "";
Print #fHnd, "<FONT FACE="""; cell.Font.Name; """ COLOR=";
Print #fHnd, vbColorToHtmlColor(cell.Font.Color); "";
If cell.Font.Bold Then Print #fHnd, "<B";
If cell.Font.Italic Then Print #fHnd, "<I";
'etc.
'any formatting you want to capture must be manually dealt with
Print #fHnd, cell.Value;
'good form to close any html tags that need it...
If cell.Font.Italic Then Print #fHnd, "</I";
If cell.Font.Bold Then Print #fHnd, "</B";
Print #fHnd, "</FONT"
Next
Print #fHnd, "</TABLE</BODY</HTML"
Close fHnd
End Sub
Function vbColorToHtmlColor(vbc As Long) As String
Dim tmp As Long, outP As String
tmp = vbc And &HFF
outP = IIf(tmp < 10, "0", "") & Hex$(tmp)
tmp = (vbc And &HFF00&) \ 256
outP = outP & IIf(tmp < 10, "0", "") & Hex$(tmp)
tmp = (vbc And &HFF0000) \ 65536
outP = outP & IIf(tmp < 10, "0", "") & Hex$(tmp)
vbColorToHtmlColor = outP
End Function

Call it like this:
rangeToHTMLFile Selection, "C:\test.htm"

Edit as you see fit.

--
I need this! I haven't been involved in any chaos for an hour!