Copy From HTML to Excel

[zakkar]

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

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

[RobDog888]

If you automate Excel you can use an Excel WebQuery to load the page directly into a sheet.

[zakkar]

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

[zakkar]

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