Copy From HTML to Excel


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Copy From HTML to Excel

  1. #1
    Join Date
    Jun 2007
    Posts
    26

    Copy From HTML to Excel

    Hi ,
    I am trying ,using VB 2005 ,to read an html file (the Html as it is) copy it in the clipboard and using the pastespecial so i can write it in Excel 2003. If we go manually we do this ,For eg I press the select all command in a Browser and press the paste special in Excel (choosing the HTML type) then this is the format I want for my xls. The problem is that I want to do this in code and the user don't have to open or see anything. Is there a way to do it ?
    Below is the code that i use but it is not seem to work. I get an excepion error.

    I have created a function where I am passing as arguments the Htmlcode and the path which I want to save my xls file.
    Of course I have used all the necessary imports
    Code:
    Private Function ExportToExcel(ByVal s_htmlcode As String, ByVal s_excelpath As String) As Integer
            'Copy a string to the clipboard
            Try
                Dim data_object As New DataObject
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim s_writetext As String
    
                s_writetext = s_excelpath
    
    
                If Convert.IsDBNull(s_htmlcode) Or Trim(s_htmlcode) = "" Then
                    Return 0
                End If
    
                If Convert.IsDBNull(s_excelpath) Or Trim(s_excelpath) = "" Then
                    Return 0
                End If
    
                Clipboard.Clear()
                data_object.SetData(DataFormats.Html, s_htmlcode)
                Clipboard.SetDataObject(data_object)
    
                'Create a new workbook in Excel
    
                oExcel = New Excel.Application
                If oExcel Is Nothing Then
                    Return 0
                End If
                oBook = oExcel.Workbooks.Add
    
                'oSheet = oBook.ActiveSheet
                oSheet = DirectCast(oBook.ActiveSheet, Excel.Worksheet)
    
                Dim data_object1 As IDataObject = Clipboard.GetDataObject
    
                If data_object.GetDataPresent(DataFormats.Html) Then
                    
                    oSheet.Range("A1:A1").Select()
                    oSheet.PasteSpecial(Format:="HTML", Link:=False, DisplayAsIcon:=False)
                    oExcel.Visible = True
                Else
                    Console.writeline("Not an  HTML Format")
    
                End If
    
                oSheet.SaveAs(s_excelpath)
    
                oSheet = Nothing
                oBook.Close()
                NAR(oBook)
                oExcel.Quit()
                NAR(oExcel)
                Return 0
            Catch Ex As Exception
                
                Throw Ex
            Finally
                GC.Collect(0)
            End Try
        End Function
    
    
        Private Sub NAR(ByVal o As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            Catch
            Finally
                o = Nothing
            End Try
        End Sub
    The project is a console application. If i use the above code with the clipboard commands in a windows application project then everything is working fine. I get the desired result. The command PasteSpecial works. Without using the Clipboard functions. Just opening the HTML file in a browser select all ,copy and then press the button from my application. But that is not the point. I want everything to work behind the scene. Passing the command line arguments and save my xls file. The error I am getting is the below line

    "Microsoft Excel cannot paste data."

    I have a suspicion that something 's happening with the following commands

    data_object.SetData(DataFormats.Html, s_htmlcode)
    Clipboard.SetDataObject(data_object)

    And I cannot get it work.
    Any Ideas ? How can I read an html file and use the pastespecial in the excel ?Using code VB 2005. ?


    Thank you
    zkar

  2. #2
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    If you automate Excel you can use an Excel WebQuery to load the page directly into a sheet.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  3. #3
    Join Date
    Jun 2007
    Posts
    26
    Hi ,
    I have tried the solution you are suggesting and I am getting the HTML code. The file is saved locally to my machine.
    Is there any code to create all this procedure by code in VB 2005?
    But I don't want to display the HTML code.

    Any other ideas ?

    Thank you
    zkar

  4. #4
    Join Date
    Jun 2007
    Posts
    26
    This is the class which a programmer gave to me and it actually works.
    Reads the Html file , copied it as text in the clipboard , pastes in excel and saves the xls file.


    'Option Strict On
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Excel ' Reference to Microsoft Excel 11.0 required.
    Imports System.IO
    Imports System
    Imports System.IO.FileStream
    Imports System.ComponentModel
    Imports System.Windows.Forms

    Public Class ParseWebPage
    Implements IDisposable
    Private disposedValue As Boolean = False ' To detect redundant calls
    Private ExcelApp As Excel.Application
    Private Shared CopyStatus As CopyHTMLStatus
    Private Shared HtmString As String

    Sub New()
    ' Initialize objects.
    CopyStatus = New CopyHTMLStatus
    End Sub

    Private Structure CopyHTMLStatus
    Friend Success As Boolean
    Friend ErrorInfo As String
    End Structure

    Public Sub LoadWebPageIntoExcel(ByVal WebPageFile As String)
    If CopyHTMLString(WebPageFile, HtmString).Success Then
    PasteAndOpenExcel(WebPageFile)
    If CopyStatus.Success = False Then
    Write_Args(CopyStatus.ErrorInfo, WebPageFile)
    End If
    Else
    Try
    Write_Args(CopyStatus.ErrorInfo, WebPageFile)
    Catch ex As Exception
    Write_Args(CopyStatus.ErrorInfo, WebPageFile)
    End Try
    End If

    End Sub

    Private Shared Function CopyHTMLString(ByVal SourceFile As String, ByVal HtmlString As String) As CopyHTMLStatus
    Clipboard.Clear()
    Dim Data_Object As New DataObject
    Dim R As IO.StreamReader
    Try

    If IO.Directory.Exists(IO.Path.GetDirectoryName(SourceFile)) AndAlso IO.File.Exists(SourceFile) Then
    R = New IO.StreamReader(SourceFile)
    HtmlString = R.ReadToEnd
    Else
    CopyStatus.ErrorInfo = "Unable to read from the Source File: " & SourceFile & " because the file path could not be found."
    End If

    If Not HtmlString.ToLower.Trim.StartsWith("<html>") Then
    HtmlString = "<html> " & HtmlString
    End If

    If Not HtmlString.ToLower.Trim.EndsWith("<html>") Then
    HtmlString &= " </html>"
    End If

    Data_Object.SetData(DataFormats.Text, HtmlString)
    Clipboard.SetDataObject(Data_Object)
    CopyStatus.Success = True
    CopyStatus.ErrorInfo = ""

    Catch ex As Exception
    CopyStatus.ErrorInfo = ex.ToString
    CopyStatus.Success = False
    Finally
    If Not R Is Nothing Then
    R.Close()
    End If
    End Try
    Return CopyStatus

    End Function

    Private Sub PasteAndOpenExcel(ByVal webpagefile As String)
    Dim Book As Workbook
    Dim Sheet As Worksheet
    Dim s_excelpath As String
    Try

    s_excelpath = webpagefile

    ExcelApp = New Excel.Application
    Book = ExcelApp.Workbooks.Add
    Sheet = DirectCast(Book.ActiveSheet, Excel.Worksheet)
    Dim data_object As IDataObject = Clipboard.GetDataObject
    Sheet.Range("A1:A1").Select()
    Sheet.Paste()
    s_excelpath = Mid(s_excelpath, 1, Len(s_excelpath) - 4)
    s_excelpath &= ".xls"
    Sheet.SaveAs(s_excelpath)

    'ExcelApp.Visible = True
    Catch ex As Exception
    CopyStatus.ErrorInfo = ex.ToString
    CopyStatus.Success = False
    Finally
    Book = Nothing
    Sheet = Nothing
    If Not ExcelApp Is Nothing Then
    Dim RefCount As Integer
    Do
    RefCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
    Loop Until RefCount = 0
    ExcelApp = Nothing
    End If
    End Try

    End Sub

    ' IDisposable

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
    If Not Me.disposedValue Then
    If disposing Then
    ' TODO: free resources if explicit is on.
    End If

    If Not ExcelApp Is Nothing Then
    Dim RefCount As Integer
    Do
    RefCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
    Loop Until RefCount = 0
    End If
    End If
    Me.disposedValue = True
    End Sub


    Private Function Write_Args(ByVal s_errormessage As String, ByVal s_path As String) As Integer
    Dim target As String
    Dim write_message As String
    Dim Pos As Int16

    s_path = Mid(s_path, 1, Len(s_path) - 4)
    s_path &= "_errorlog.txt"

    target = "Old format or invalid type library"
    Pos = InStr(1, s_errormessage, target)
    If Pos > 0 Then
    write_message = "Copy Excel.Exe file into the 1033 located = C:\Program Files\Microsoft Office\OFFICE11\1033 (if you don't have create the folder) and rename it to xllex.dll" & vbCrLf
    write_message &= s_errormessage
    Else
    write_message = s_errormessage
    End If
    'for debug reasons only
    Dim objStreamWriter As StreamWriter
    objStreamWriter = New StreamWriter(s_path)
    objStreamWriter.WriteLine(write_message)
    'Close the file.
    objStreamWriter.Close()
    End Function


    #Region " IDisposable Support "
    Public Sub Dispose() Implements IDisposable.Dispose

    ' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.

    Dispose(True)

    GC.SuppressFinalize(Me)

    End Sub

    #End Region

    End Class

Similar Threads

  1. Replies: 1
    Last Post: 06-04-2007, 05:19 PM
  2. Replies: 0
    Last Post: 09-09-2006, 07:21 AM
  3. Replies: 0
    Last Post: 12-23-2005, 01:26 PM
  4. Replies: 2
    Last Post: 11-14-2005, 04:35 PM
  5. Replies: 0
    Last Post: 08-15-2005, 05:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center