-
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>
-
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]
