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
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
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.
'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
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
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.
