asked on

How to assign object values to datatable in vb.net ?

Here is my vb.code. i want to loop through object svalue and assign values to datatable. How to do this ?

 Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        string workbookPath = @"c:\temp\SomeWorkBook.xlsx";
        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(workbookPath , Type.Missing, false, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        xlApp.Visible = false;
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        object svalue = xlWorkSheet.UsedRange.Value2;

Open in new window

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Varshini S

ASKER

Yes. You are correct.

i have converted your code vb.net. But it showing an below error when executing following line

System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=@'C:\\Temp\\SomeWorkBook.xlsx';Extended Properties=Excel 8.0;");

Error: Format of the initialization string does not conform to specification starting at index 34.

My Code:

  System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=@'C:\\Temp\\SomeWorkBook.xlsx';Extended Properties=Excel 8.0;");
             System.Data.OleDb.OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cnn);
            System.Data.DataSet data = new System.Data.DataSet();
            cmd.Fill(data);

Open in new window

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mlanda T

System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Temp\SomeWorkBook.xlsx';Extended Properties=Excel 8.0;");

You syntax is slightly incorrect. Try this.