none
Export Excel data to HTML using VB.Net in visual RRS feed

  • Question

  • In windows form need to export data from excel to HTML. I am trying as below but getting "Public member 'NamedaRanges' on type 'Worksheet' not found" in namedRanges. Not able to verify after that whether it will save HTML successfully. Please help to solve this.

    '''
        Private Sub Button_click() 
            xlSheet=xlWB.Worksheets("Sheet3")  
            xlSheet.NamedRanges.SetPrintArea(xlSheet.Cells.GetSubrange
                                                                                                          ("A1",C1")) 
            xlSheet.SaveAs(FileName:="C:\Users\ Sample.html",                                                                                                        FileFormat:=xlHtml)
         End Sub

    '''

    • Edited by Abi0207 Thursday, March 19, 2020 6:21 AM
    Thursday, March 19, 2020 6:08 AM

All replies

  • Hi Abio207,

    I suppose that definition of WorkBook and opening the WorkBook, and saving the WorkBooklwould be needed.
    (WorkSheet cannot be saved as a file) 

    Regards,

    Ashidacchi -- http://hokusosha.com

    Thursday, March 19, 2020 7:12 AM
  • Ok. But am getting error on before line with namedranges. How data from excel sheet specific columns can be exported to HTML?
    • Edited by Abi0207 Thursday, March 19, 2020 7:33 AM
    Thursday, March 19, 2020 7:22 AM
  • Hi,

    Do you have "NamedRange" in the worksheet?

    Regards,

    Ashidacchi -- http://hokusosha.com

    Thursday, March 19, 2020 7:37 AM
  • No. Can it be done in run time in VB? Can it be handled in script?
    Thursday, March 19, 2020 7:48 AM
  • Hi,

    1) You need to specify whether it is Syntax error or Run-time error. 
    2) What do you want to do with this code?
     xlSheet.NamedRanges.SetPrintArea(xlSheet.Cells.GetSubrange("A1",C1")) 

    Regards,

    Ashidacchi -- http://hokusosha.com

    Thursday, March 19, 2020 8:26 AM
  • It is run time error. I need A,B,C columns from excel to be exported as HTML report.
    Thursday, March 19, 2020 8:53 AM
  • No I can't able to use those. Showing unwanted imports statement unnecessary.
    Thursday, March 19, 2020 9:21 AM
  • Hi Abi0207,

    I've made a sample.

    Source Excel file:
        

    Saved HTML file  (viewed in Google Chrome browser):
       

    VB.NET code:
    ' ---[Export to HTML] button
    Private Sub btn_Export_to_HTML_Click(sender As Object, e As EventArgs) Handles btn_Export_to_HTML.Click
    	Dim inputFile As String = "C:\Test\Test_Excel.xlsx"
    	Dim outputFile As String = "C:\Test\Test_Excel.html"
    	If (System.IO.File.Exists(outputFile)) Then
    		System.IO.File.Delete(outputFile)
    	End If
    	' ---
    	Dim xlApp As New Microsoft.Office.Interop.Excel.Application
    	Dim xlSheet As New Microsoft.Office.Interop.Excel.Worksheet
    	' --- 
    	With xlApp
    		' --- Open a Book, add a sheet, and name it
    		.Workbooks.Open(inputFile)
    		.Worksheets.Add()
    		.ActiveSheet.name = "Sheet" & .Worksheets.Count
    		' --- set Sheet3 to xlSheet
    		xlSheet = xlApp.Worksheets("Sheet3")
    		' --- set data and save as HTML file
    		.ActiveSheet.range("A:C").value = xlSheet.Range("A:C").Value
    		.ActiveSheet.SaveAs(outputFile,
    				  FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml)
    	End With
    	' --- release object
    	xlApp.Quit()
    	System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
    	System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
    	' ---
    	MsgBox("[" & outputFile & "] has been saved!")
    End Sub
    
    Private Sub btn_Open_HTML_Click(sender As Object, e As EventArgs) Handles btn_Open_HTML.Click
    	Dim inputFile As String = "C:\Test\Test_Excel.html"
    	Dim p As System.Diagnostics.Process _
    		= System.Diagnostics.Process.Start(inputFile)
    End Sub

    [note]
       If there are 5 sheets in the source Excel file, the saved HTML file will have 5 sheets in it. So, if you want to leave only one sheet in the HTML file, you need to delete other sheets. (as for my code, delete all sheets except the last sheet)

    Regards,

    Ashidacchi -- http://hokusosha.com



    • Edited by Ashidacchi Friday, March 20, 2020 1:05 AM
    • Proposed as answer by Ashidacchi Friday, March 20, 2020 1:06 AM
    • Unproposed as answer by Ashidacchi Saturday, March 21, 2020 3:51 AM
    Friday, March 20, 2020 1:00 AM
  • Hi,

    Have you solved this problem now?

    I think the above reply can provide you with a solution, have you tried it?

    If so, hope you can close this thread by marking the reply as answer as this will help others looking for the same or similar issues down the road.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 20, 2020 9:20 AM
    Moderator
  • Hi Abio207,

    How is your issue?
    If my previous post is helpful, please mark my post as an answer.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Saturday, March 21, 2020 3:49 AM