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
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
Private Function ExportToExcel(ByVal s_htmlcode As String, ByVal s_excelpath As String) As Integer
'Copy a string to the clipboard
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
If Convert.IsDBNull(s_excelpath) Or Trim(s_excelpath) = "" Then
'Create a new workbook in Excel
oExcel = New Excel.Application
If oExcel Is Nothing Then
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.PasteSpecial(Format:="HTML", Link:=False, DisplayAsIcon:=False)
oExcel.Visible = True
Console.writeline("Not an HTML Format")
oSheet = Nothing
Catch Ex As Exception
Private Sub NAR(ByVal o As Object)
o = Nothing
"Microsoft Excel cannot paste data."
I have a suspicion that something 's happening with the following commands
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. ?