Converting Excel to HTML

**cvaden** · Jun 5th, 2000, 02:46 AM

Ok here goes....

I have a macro that was created with VBA in Excel.

I wanted to move this to a stand-alone VB app.

I only have one more little problem. I can't access the htmlconvert function. This function resides in "HTML.XLA".
I can only add this as a related document in VB and I don't know where to go from here.

I have looked everywhere!!! Someone PLEASE HELP!!!!

***Function Example as Used in VBA***

createOK = htmlconvert(ActiveSheet.ChartObjects("chart 1"), False, False, False, 1252, HTMLFile & "u" & Trim$(Str(Nodes)) & " Load " & Format(rundate, "ddd" & ".htm" , , , , "USAN_" & Trim$(Str(Nodes)) & " Load " & Format(rundate, " mmmm dd, yyyy"), True, , Now, CurrentUser, CurrentUserEmail)

<C>

**Mongo** · Jun 6th, 2000, 03:33 AM

This isn't the 100% solution, but it should help get you started or at least give you a couple ideas.
It's not complete, but, hey, it's off the top of my head & I don't do much HTML... Hope it helps

Code:

Sub ExportRng2HTML(srcWB As String, srcWS As String, srcAddy As String,
  _ tgtFile As String, tblSz As String, UseRngColWid As Boolean, 
  _ TblBorderSz As Integer, CellPadding As Integer, CellSpacing As Integer, 
  _ IncludeEmptyCells As Boolean) 
' Export data in Workbooks(srcWB).Worksheets(srcWS).Range(srcAddy) to textfile in HTML format 
' Example: ExpRng2HTML ThisWorkbook.Name, "ExportSheet", "A3:E23", "C:\temp\myHtml.htm", "", True, 1, 5, 0 
Dim orgRange As String, scrRange As Range 
Dim A As Integer, c As Integer, r As Long, totRows As Long, pror As Long 
Dim fn As Integer, lnStr As String, tLine As String, CellColumnWidth As Long 
Dim BoldCell As Boolean, ItalicCell As Boolean, CellAlignment As Integer 
  Workbooks(SourceWB).Activate 
  Worksheets(SourceWS).Activate 
  If Application.WorksheetFunction.CountA(Range(srcAddy)) = 0 Then 
    If Not IncludeEmptyCells Then Exit Sub
  End If
  If Dir(tgtFile) <> "" Then 
    On Error Resume Next 
    Kill tgtFile On Error GoTo 0 
    If Dir(tgtFile) <> "" Then 
      MsgBox tgtFile & " already exists, rename, move or delete the file before you try again.", _
        vbInformation, "Export range to textfile" 
      Exit Sub 
    End If 
  End If 
  ' perform export 
  Set scrRange = Range(srcAddy) 
  On Error GoTo oops 
  fn = FreeFile 
  Open tgtFile For Append As #fn  
  On Error GoTo 0 
  ' determine total number of rows to process 
  totRows = 0 
  For A = 1 To scrRange.Areas.Count 
    totRows = totRows + scrRange.Areas(A).Rows.Count 
  Next A 
  ' start HTML file 
  Print #fn, "<html><head>"
'  Print #fn, "<meta name = ""DESCRIPTION"" content=""{your description here}"">"
'  Print #fn, "<meta name=""KEYWORDS"" content=""{your keywords}"">"
  Print #fn, "<title>Range to HTML from " & ActiveWorkbook.Name & "</title>"
  Print #fn, "</head><body background="/images/bground.gif" bgcolor="#ffffff" text="#000000" marginheight="0" topmargin="0">"
  Print #fn, "<h1>Range to HTML: " & ActiveWorkbook.Name & "</h1>" 

'  NOTE: It is screwy (screwier? *smirk*) past here.
'  But you should get the idea...

  If tblSz = "" Then 
    Print #fn, "<table border=" _
  Else 
    Print #fn, "" 
    pror = 0 
    For A = 1 To scrRange.Areas.Count 
      For r = 1 To scrRange.Areas(A).Rows.Count 
        If pror Mod 50 = 0 Then _
          Application.StatusBar = "Writing HTML-file " & Format(pror / totRows, "0 %") & "..." 
        If Print #fn, " " 
        For c = 1 To scrRange.Areas(A).Columns.Count 
          lnStr = " " 
          CellAlignment = 0 
          tLine = "" 
          On Error Resume Next 
          With scrRange.Areas(A).Cells(r, c) 
            tLine = Trim(.Value) 
            BoldCell = .Font.Bold 
            ItalicCell = .Font.Italic 
            CellAlignment = .HorizontalAlignment 
          End With 
          On Error GoTo 0 
          If (tLine = "" Or tLine = " ") And IncludeEmptyCells Then 
            tLine = "" 
            If tLine <> "" Then 
              lnStr = lnStr & "<td" 
            If UseRangeColumnWidths Then 
              CellColumnWidth = CLng(Cells(1, c + 1).Left - Cells(1, c).Left) 
            lnStr = lnStr & " width=""" & CellColumnWidth & """" 
          End If 
          If CellAlignment = xlHAlignCenter Then 
            lnStr = lnStr & " align=""center""" 
          If CellAlignment = xlHAlignRight Then 
            lnStr = lnStr & " align=""right""" 
          lnStr = lnStr & "" 
          If BoldCell Then lnStr = lnStr & "" 
          If ItalicCell Then lnStr = lnStr & "" 
          lnStr = lnStr & tLine 
          If ItalicCell Then 
            lnStr = lnStr & "" 
          If BoldCell Then lnStr = lnStr & "" 
            lnStr = lnStr & "" 
          Print #fn, lnStr 
        End If 
      Next c 
      Print #fn, " " 
      pror = pror + 1 
    Next r Next A 
    ' finish up HTML file 
    ' Print #fn, "</table>"   yada, yada...
    Print #fn, "</body>,</html>"    
    Close #fn 
oops: 
  Set scrRange = Nothing 
  Application.StatusBar = False 
End Sub

[Edited by Mongo on 06-06-2000 at 04:40 PM]

Thread: Converting Excel to HTML

Thread Tools

Display

Posting Permissions