excel

[Bvisual]

could someone give me a code example how data from a excel file can be loaded in a array whitout the excel file being opend.

I have a code that is almost working but the problem is that the excel file seems to be staying open so i cant read or write to the excel file.

 

[SqueakinSweep]

It sounds as if the Excel process is not being properly disposed. This has been covered in this forum several times of late. Take a look at a recent thread on this subject thread796-1081776

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[Rhys666]

You can use an OleDbConnection from within the System.Data.OleDb namespace to utilise Excel as a data source, rather like a database, rather than opening and reading it as an excel file. I've used this to fill a DataSet from an Excel 2002 source but unfortunately it's c# and I don't have ther time to convert it myself. It means it'll need some tweaking to convert it to VB.Net and to put the data into an array instead but you should be able to get the general idea.

private DataSet GetDataSetFromExcelWorksheet( string filePathName, int workSheetNumber )
{
	OleDbConnection excelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filePathName+";Extended Properties=Excel 8.0;");
	OleDbCommand excelCommand = new OleDbCommand();
	excelCommand.Connection = excelConnection;
	OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand);
	DataSet excelDataSet = new DataSet();

	try
	{
		excelConnection.Open();
		DataTable excelSheet = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});
		string SpreadSheetName = "["+excelSheet.Rows[workSheetNumber]["TABLE_NAME"].ToString()+"]";

		excelCommand.CommandText = @"SELECT * FROM "+SpreadSheetName;
		excelAdapter.Fill(excelDataSet);
		excelConnection.Close();
	}
	catch( Exception ex )
	{
		//Error Handling Here
	}
	finally
	{
		excelConnection.Close();
		excelAdapter.Dispose();
		excelCommand.Dispose();
		excelConnection.Dispose();
	}

	return excelDataSet;
}

Rhys
"There are some oddities in the perspective with which we see the world. The fact that we live at the bottom of a deep gravity well, on the surface of a gas-covered planet going around a nuclear fireball 90 million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be"
DOUGLAS AD

 

[SqueakinSweep]

Putting Rhys;s code through a C# to VB convertor results in

Private Function GetDataSetFromExcelWorksheet(filePathName As String, workSheetNumber As Integer) As DataSet
   Dim excelConnection As New OleDbConnection("")
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePathName + ";Extended Properties=Excel 8.0;" '
   'ToDo: Error processing original source shown below
   '
   '
   '------------------------------------------------------------^--- Syntax error: ')' expected
   '
   'ToDo: Error processing original source shown below
   '
   '
   '----------------------------------------------------------------------------------------------------------------------------------------------------------^--- Syntax error: ';' expected
   Dim excelCommand As New OleDbCommand()
   excelCommand.Connection = excelConnection
   Dim excelAdapter As New OleDbDataAdapter(excelCommand)
   Dim excelDataSet As New DataSet()
   
   Try
      excelConnection.Open()
      Dim excelSheet As DataTable = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
      Dim SpreadSheetName As String = "[" + excelSheet.Rows(workSheetNumber)("TABLE_NAME").ToString() + "]"
      
      excelCommand.CommandText = ""
      "SELECT * FROM " + SpreadSheetName '
      'ToDo: Error processing original source shown below
      '
      '
      '-------------------------------------^--- Syntax error: ';' expected
      excelAdapter.Fill(excelDataSet)
      excelConnection.Close()
   Catch ex As Exception
   'Error Handling Here
   Finally
      excelConnection.Close()
      excelAdapter.Dispose()
      excelCommand.Dispose()
      excelConnection.Dispose()
   End Try
   
   Return excelDataSet
End Function 'GetDataSetFromExcelWorksheet

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[Bvisual]

this is the code i got so far.
it keaps opening excel but the data read is good
but after a while the pc will crash.

Whats wrong with it?????

'LOADING DATA from EXCEL FILE

        Dim xlApp As New Excel.Application
        Dim WB As Excel.Workbook
        Dim WS As Excel.Worksheet

        WB = xlApp.Workbooks.Open("c:\TEST.xls")
        WS = WB.Worksheets(1)     ' open sheet 1
        WS.Select()
        xlApp.Visible = True



        Dim tafel As Integer
        Dim telK As Integer = 1
        Dim telR As Integer = 2
        Dim Userindex As Integer = 0

        Dim Tel1 As Integer = 0
        Dim Tel2 As Integer = 0

        Do While Tel2 < 10
            Tel2 = Tel2 + 1
            Do While Tel1 < 9
                Tel1 = Tel1 + 1
 User(Tel1, 1, Tel2) = xlApp.Worksheets(1).cells(Tel2 + 2, Tel1 + 1).Value()
            Loop

            Tel1 = 0

        Loop
        MsgBox(User(1, 1, 1))
        MsgBox(User(5, 1, 3))

       

        'closing down workbook
        xlApp.Workbooks.Close()
        WB.Workbooks.Close()
        WS.Workbooks.Close()

        xlApp.dispose()
        WB.dispose()
        WS.dispose()

 

[SqueakinSweep]

Dispose of the sheet before the workbook and before xlapp.
Also look at the thread I pointed you to in terms of releasing com objects.

Why also are you making excel visible?

Code to close Excel & clean/dispose

oExcelBook.Sheets.Clear()

xlapp.Workbooks.Close()
xlapp.Quit()

While System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp) <> 0
End While

xlapp = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[Bvisual]

can you adjust or give me a proper code that does the same because i keap getting a error message.

i dont realy now how to get it right
thx

 

[SqueakinSweep]

I could but I wont...if you cant figure out the miniscule changes to the above then I find it hard to believe you got anything to Excel in the first place. I do believe you only need to change one line of code I posted to make it work for you





Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[Bvisual]

i fixed the problem

...

what is the command for adjusting kolums and rows hight,
page properties, adjusting the marges, hiding empty rows?

BTW thx for the help sweep

 

[earthandfire]

If you record a macro in Excel in which you carry out all of the required actions (column width, row height etc), then have a look at the code produced by macro recorder.

It would then be a relatively straightforward excercise to transfer that code to your VB.Net project. I often use that technique and it can save hours that would otherwise be spent trying to make sense of the Excel object model.

Hope this helps.