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

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

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

06-20-2007, 02:20 PM

RobDog888

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

06-21-2007, 02:12 AM

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

06-25-2007, 07:30 AM

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.

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

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

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