• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 934
  • Last Modified:

Export HTML page in Webbrowser control to Microsoft Excel

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 ?
0
aganapathy
Asked:
aganapathy
  • 3
1 Solution
 
edwardiiiCommented:
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, _
        Destination:=Range("A1"))
        .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
0
 
aganapathyAuthor Commented:
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
    .Refresh
End With
Xl.Application.Visible = True
0
 
edwardiiiCommented:
That's great news.  Can you alter the following to streamline your code?:

From:

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

To:

Set qtQtrResults = XlWs.QueryTables.Add(Connection:="URL; & currentURL", Destination:=XlWs.Cells(1, 1))
0
 
edwardiiiCommented:
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:)
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now