Server exception when opening a excel file in VB.net

[RinoStoof]

Hi there

I am trying to open an existing spreadsheet from VB.net 2005.

I used the code as given else where on your site:

Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsCell As Excel.Range
Dim xlsDatei As String

xlsApp = New Excel.Application
xlsApp.Visible = True
xlsWB = xlsApp.Workbooks.Open("c:\123.xls") *** my problem ***
xlsSheet = xlsWB.Worksheets(1)
xlsCell = xlsSheet.Range("A1")

but I get an error as follows when it comes to opening the file (when making xlsApp visible excel itself does open, just not the file..):

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2147417851
Message="The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"
Source="Interop.Excel"
StackTrace:
at Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)

So far I have wasted an afternoon trying to do this basic thing and I would be wrapped if someone could give me a little shove in the back :-)

Thanks very much!
Rino

PS Sorry, not too sure on how to do this code wrapping..
R

[YHUBZ]

Helloo There... Try this Code when opening Excel..

Public Class ExcelConnection

'Import Excel Data
Public Function Import_Excel_File(ByVal PrmPathExcelFile As String, ByVal uDataGridView As DataGridView)

Dim MyConnection As System.Data.OleDb.OleDbConnection

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")

Dim DtSet As System.Data.DataSet

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
Try

''''''' Fetch Data from Excel

' Select the data from Sheet1 of the workbook.

MyCommand.TableMappings.Add("Table", "Attendence")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
uDataGridView.DataSource = DtSet.Tables(0)
MyConnection.Close()

Catch ex As Exception
MyConnection.Close()
End Try
Return True
End Function

Note: Verify first the version of your excel (i'm using version 8)

[YHUBZ]

Sorry I forgot the syntax....

This is how you call the class...

Dim MyGridView As New ImportExcelConnection.ExcelConnection
MyGridView.Import_Excel_Data(Txt_Path.Text, DataGridView1)

Note: DataGridView1 is object place on you form

[RinoStoof]

Hi WHUBS

Hey that works an absolute treat! Thank you very much for your help! You've saved my head as well as the wall :-)

Kind regards
Rino

[RinoStoof]

Hi WHUBS

I have been having a few problems with the above project..

I'm importing info for tires from the attached spreadsheet (no idea why it is so large sorry).

To get the categories (passenger, truck tc.) I have to add in a formula to make it a row in the dataset. for obvious reasons the dataset ignores the large headers. Unfortunately I need them.
The same with the rim diameter.

For some reason the 'tread' column just doesn't show so I have to copy that into another column which it then does pick up ??

I can't really expect our customers to do this manually before importing.

In VB6 I used to just go into the sheet and do whatever I wanted. Not as elegant as your method but at least I could make it do what I wanted.

So all in all I would really like to be able to open and manually manipulate the sheet.
As described above I didn't have much luck with that..

Can you, or anyone else, help please?

Thanks very much in advance!

[YHUBZ]

Hello RinoStoof...

I have seen your problem and try your excel file...

Try this if you like, this time use CSV format, not the Excel file.

this is the code:

Imports System.IO
Public Class ImportExport

Private Sub btnCSV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCSV.Click
Dim TransferOK As Boolean = False
Dim vdataTable As DataTable

Try
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
vdataTable = GET_CSV_DATA(OpenFileDialog1.FileName, True)
DataGridView1.DataSource = vdataTable
End If

TransferOK = True
Catch ex As Exception
MessageBox.Show(ex.Message)
TransferOK = False
End Try

End Sub

Function GET_CSV_DATA(ByVal sCSVPathandFile As String, ByVal bFirstRowColName As Boolean) As DataTable
Dim file_contents As String
Dim records() As String
Dim field_names() As String
Dim record_num As Integer
Dim k As Integer
Dim ds As New DataSet("CsvData")
Try

System.Windows.Forms.Cursor.Current = Cursors.WaitCursor

ds.Tables.Add("Data")

Const DELIMITER = ","

file_contents = GetCSVContents(sCSVPathandFile)

records = Split(file_contents, vbCrLf)

field_names = Split(records(0), DELIMITER)

For record_num = 1 To records.GetUpperBound(0)
Application.DoEvents()
records(record_num) = records(record_num).Trim
If records(record_num).Length >= 1 Then
If record_num = 1 Then
field_names = Split(records(0), ",")

If bFirstRowColName Then
For k = 0 To UBound(field_names)
ds.Tables("Data").Columns.Add(Trim(field_names(k)))
Next
Else
For k = 0 To UBound(field_names)
ds.Tables("Data").Columns.Add("Column" & Trim(k))
Next
End If
End If
ds.Tables("Data").Rows.Add(Split(records(Trim(record_num)), ","))
End If
Next record_num
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
GET_CSV_DATA = ds.Tables("Data")

End Function

Function GetCSVContents(ByVal file_name As String) As String
Dim strm As New StreamReader(file_name)
Dim txt As String = strm.ReadToEnd()

strm.Close()
Return txt
End Function
End Class

'This all i can help, you can customize the importing..