Excel range to WebBrowser vba

Question

Dear all,

by this code I'm able to populate a WebBrowser placed in my VBA userform

Public Function ConvertRangeToHTMLTable(rInput As Range) As String 

    Dim rRow As Range 

    Dim rCell As Range 

    Dim strReturn As String 

    strReturn = "<Table border='1' cellspacing='0' cellpadding='7' style='border-collapse:collapse;border:none'><font size='2' face='Calibri' color='black'>  " 

    For Each rRow In rInput.Rows 

        strReturn = strReturn & " <tr align='Center'; style='height:10.00pt'> " 

        For Each rCell In rRow.Cells 

            If rCell.Row = 1 Then 

                strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'><b>" & rCell.Text & "</b></td>" 

            Else 

                strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'>" & rCell.Text & "</td>" 

            End If 

        Next rCell 

        strReturn = strReturn & "</tr>" 

    Next rRow 

    strReturn = strReturn & "</font></table>" 

    ConvertRangeToHTMLTable = strReturn 

End Function

Private Sub cmdShowPage_Click() 

    Dim HTML As String 

    HTML = ConvertRangeToHTMLTable(ThisWorkbook.Worksheets("Export").Range("A1:Q19965")) 

    WebBrowser1.Navigate "://about blank" 

    DoEvents 

    With WebBrowser1.Document 

        .write HTML 

    End With 

End Sub

The issue is the function ConvertRangeToHTMLTable which takes a long if the range contains lot of data (likely 20000 rows and 17 columns) in fact I'm thinking to give the range as source of my WebBrowser1 and doing something like:

WebBrowser1.Navigate ThisWorkbook.Worksheets("Export").Range("A1:Q19965")

but I'm not able to do it: can you help me?

Thanks

Answer 1

Hi Lucausa75!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

Try the ConvertRangeToHTMLTable function to load a range directly into a WebBrowser control using the code below:

Private Sub cmdShowPage_Click() Dim rng As Range Dim tempWB As Workbook Dim tempWS As Worksheet Dim tempHTMLFile As String

'Set the range to be loaded into the WebBrowser control Set rng = ThisWorkbook.Worksheets("Export"). Range("A1:Q19965")

'Create a new workbook and worksheet to store the range data Set tempWB = Workbooks.Add Set tempWS = tempWB.Sheets(1)

'Copy the range to the new worksheet rng. Copy tempWS.Range("A1"). PasteSpecial xlPasteAll

'Save the new workbook as an HTML file tempHTMLFile = ThisWorkbook.Path & "\temp.html" tempWB.SaveAs tempHTMLFile, xlHtml

'Navigate the WebBrowser control to the HTML file WebBrowser1.Navigate tempHTMLFile

'Cleanup: close the temporary workbook and delete the HTML file tempWB.Close False Kill tempHTMLFile End Sub

This code creates a new workbook and worksheet to temporarily store the range data. It then copies the range to the new worksheet and saves the workbook as an HTML file. Finally, it navigates the WebBrowser control to the HTML file.

Best Regards, Shakiru

Answer 2

Thanks!

Your code is good and works like a charm but:

1. In case of table having many rows it still takes a long time
2. Columns need to be auto-resized according with the text content
3. Kill tempHTMLFile code gives the below error

4. Any chance to remove the highlighted section below?

Thanks