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
s_htmlcode is the Html code which I have read from the htm file
and s_excelpath is the path that I want to save my xls file.
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
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
the NAR Sub is the following
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 because i want to use it from another application passing command line arguments. Everything is working fine until it goes to the pastespecial command. The exception I get is the following
“Microsoft Office Excel cannot paste the data.”
Any ideas ?
Thank you
zkar
