Where's the problem in this code? Plese help me !

Question

1.00/5 (1 vote)

See more:

Error message :
Object reference not set to an instance of an object. at this line :

VB

Dim r As DataRow = ds.Tables("Sheet1").NewRow

i want to import data from excel file from column A to column H ??

VB

Dim SourceBookPath As String = Nothing

Me.OpenFileDialog1.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx"

Me.OpenFileDialog1.ShowDialog()

If Not Me.OpenFileDialog1.FileName Is Nothing Then

SourceBookPath = Me.OpenFileDialog1.FileName End If

'=======================================================================================================================

Dim MsExcel = CreateObject("Excel.Application")

Dim ExcelBook = MsExcel.Workbooks.Open(SourceBookPath)

Dim con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceBookPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1"

Dim str As String = "select * from Sheet1"

Dim ds As New DataSet

Dim I, J As Integer

Dim lastRow As Long = 0

lastRow = MsExcel.Range("A15").Find("*", MsExcel.Range("A15"), XlFindLookIn.xlValues, XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).row

For I = 8 To lastRow

Dim r As DataRow = ds.Tables("Sheet1").NewRow

For J = 1 To 8 r(J - 1) = MsExcel.Cells(I, J).Value

Next J

ds.Tables("Sheet1").Rows.Add(r)

Next I

DataGridView1.DataSource = ds.Tables("Sheet1")

Posted 11-Jul-12 2:30am

Ahmad Salami

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2012-07-11T02:38:00

Solution 1

Run your code in the Visual Studio Debugger (f5) and it will stop and show you where the problem is.

Most probably the ds variable is null and should be initialized first.

Looking at your code you should connect to the excel file and fill the dataset first.

Posted 11-Jul-12 2:38am

Mehdi Gholam

fixthebugg · Answer 2 · 2012-07-11T03:23:00

This will get the data from the excel and put it in the dataset. Then you can do whatever you want to do with it.

Dim MyConnection As System.Data.OleDb.OleDbConnection
       Dim myPath As String = filepath
       Try
           Dim DS As System.Data.DataSet
           Dim strPath, strSheet As String
           strPath = filepath
           strSheet = "Sheet1"


           Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
           MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties='Excel 12.0 Xml;HDR=YES'")

           ' Select the data from Sheet1 of the workbook.

           MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & strSheet & "$]", MyConnection)
           DS = New System.Data.DataSet
           MyCommand.Fill(DS)

           If Not DS Is Nothing Then
               Return DS
           Else
               Return Nothing
           End If

       Catch exp As Exception
           MsgBox(exp.Message)
           Return Nothing
       End Try