Export HTML page in Webbrowser control to Microsoft Excel

Posted on 2005-04-26
Last Modified: 2012-06-27
I have created a report using HTML and that is view over webbrowser control. Now I want export the report to Excel using excel's Import Wizard -> New Web Query. In that I have a full control over my HTML Report like whether I need  in Rich Text Format, Web Query Redirections etc. Presently I'm able to do this by Right Click the HTML Report and in that if I click "Export to Excel" then I get these things. But I want to get it thorough Command Button. When I click Command Button I should be able to get the New Web Query wizard of Excel with the current HTML page. any one help ?
Question by:aganapathy
    LVL 10

    Expert Comment

    Hi, aganapathy.

    I got the following by recording a macro in Excel.  If you're doing this in VB 6 then you'll want to add a reference to the
    Excel object library and create a new Excel object, etc.:

         Dim myExcel As Excel.Application
         If myExcel Is Nothing Then
             Set myExcel = CreateObject("Excel.Application")
         End If
         myExcel.Visible = True
         myExcel.Workbooks.Open ("path/name to/of your Excel file")

    Note, if you go with the above, you'll want to alter the "ActiveSheet.Query" to include the "myExcel." preface.

    Not sure how to allow you to click a form button and have the New Web Query wizard open, but you can select the various formatting options and assign them to different buttons. The example below applies RTF; you could label the button that launches it as "RTF".  currentURL grabs the current URL of your WebBrowser control:

        Dim currentURL As String
        currentURL = WebBrowser1.LocationURL

        With ActiveSheet.QueryTables.Add(Connection:="URL;" & currentURL, _
            .Name = "www.msdn"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingRTF
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With

    Author Comment

    Hi edward,
    I tried the following and it works fine.

    Dim Xl As Excel.Application, XlWb As Excel.Workbook, XlWs As Excel.Worksheet, qtQtrResults As QueryTable
    Dim currentURL As String
    Wb.Navigate "\\sayadsl\Software Div\New SayaMatrix\ReportStyles\SayaMatrix394312.htm"
    currentURL = Wb.LocationURL
    If Xl Is Nothing Then
        Set Xl = CreateObject("Excel.Application")
    End If
    Set XlWb = Xl.Workbooks.Add
    Set XlWs = XlWb.Worksheets(1)
    Set qtQtrResults = XlWs.QueryTables _
        .Add(Connection:="URL;\\sayadsl\Software Div\New SayaMatrix\ReportStyles\SayaMatrix394312.htm", Destination:=XlWs.Cells(1, 1))
    With qtQtrResults
        .WebSingleBlockTextImport = True
        .WebDisableRedirections = True
        .WebFormatting = xlWebFormattingRTF
    End With
    Xl.Application.Visible = True
    LVL 10

    Expert Comment

    That's great news.  Can you alter the following to streamline your code?:


    Set qtQtrResults = XlWs.QueryTables _
        .Add(Connection:="URL;\\sayadsl\Software Div\New SayaMatrix\ReportStyles\SayaMatrix394312.htm", Destination:=XlWs.Cells(1, 1))


    Set qtQtrResults = XlWs.QueryTables.Add(Connection:="URL; & currentURL", Destination:=XlWs.Cells(1, 1))
    LVL 10

    Expert Comment

    Sorry, the correct code is:

    Set qtQtrResults = XlWs.QueryTables.Add(Connection:="URL;" & currentURL, Destination:=XlWs.Cells(1, 1))

    Also, for me using Excel 2000, the ".WebDisableRedirections = True" isn't an option.  I also have to navigate to the
    Web page prior to executing your code (e.g. either in a separate routine or with a DoEvents loop).  And the end results
    are the same as my originally-posted solution--I'm not presented with a New Web Query Wizard pop-up:)

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now