object reference not set to an instance of an object error

14 Jul 2006 9:13 PM

dotnetnoob

i got this program that will fetch the data in the excel spreadsheet, it was
working before then i make some adjustment and it now give me an error of
"Object reference not set to an instance of an object error" i can't seem to
see what's wrong with my code so i'm hoping some fresh eyes can spot what's
wrong or what i'm missing. the following is the code, this line   strFile =
CStr(xlSheet.Range("I" + CStr(Row)).Value.ToString + "\" + strfilename) gave
me the object reference not set error. it suppose to fetch the data from
spreadsheet and tell it what directory to copy the file from the I column of
the spreadsheet contain the directory on a server.

any help is much appericated

thank

Dim o As OpenFileDialog
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim strName As String
        Dim strAddress As String
        Dim strType As String
        Dim strFile As String
        Dim strEnc As String
        Dim strSubnet As String
        Dim strTag As String
        Dim strEventP As String
        Dim strEventYN As String
        Dim Row As Integer = 2

        arlsName = New ArrayList
        arlsAddress = New ArrayList
        arlsType = New ArrayList
        arlsFile = New ArrayList
        arlsEnc = New ArrayList
        arlsSubnet = New ArrayList
        arlsTag = New ArrayList
        arlsEventP = New ArrayList
        arlsEventYN = New ArrayList

        o = New OpenFileDialog
        o.Title = "Open Excel spreadsheet"
        o.InitialDirectory = "C:\"
        o.FilterIndex = 2
        o.RestoreDirectory = True
        o.Filter = "All Files (*.xls)|*.xls"

        If (o.ShowDialog = Windows.Forms.DialogResult.OK) Then
            lblDisplay.Text = o.FileName
            xlApp = CType(CreateObject("Excel.Application"),
Microsoft.Office.Interop.Excel.Application)
            xlApp.Visible = False
            xlApp.Workbooks.Open(o.FileName)
            xlWB = xlApp.ActiveWorkbook
            xlSheet = CType(xlWB.Sheets(2),
Microsoft.Office.Interop.Excel.Worksheet)

            Dim strfilename As String = "logicaldevice.xml"

            Try
                Do
                    strName = CStr(xlSheet.Range("A" + CStr(Row)).Value)
                    strAddress = CStr(xlSheet.Range("C" + CStr(Row)).Value)
                    strType = CStr(xlSheet.Range("F" + CStr(Row)).Value)
                    strFile = CStr(xlSheet.Range("I" +
CStr(Row)).Value.ToString + "\" + strfilename)
                    strEnc = CStr(xlSheet.Range("E" + CStr(Row)).Value)
                    strSubnet = CStr(xlSheet.Range("D" + CStr(Row)).Value)
                    strTag = CStr(xlSheet.Range("H" + CStr(Row)).Value)
                    strEventP = CStr(xlSheet.Range("L" + CStr(Row)).Value)
                    strEventYN = CStr(xlSheet.Range("K" + CStr(Row)).Value)

                    If strName IsNot Nothing Then
                        arlsName.Add(strName)
                        arlsAddress.Add(strAddress)
                        arlsType.Add(strType)
                        arlsFile.Add(strFile)
                        arlsEnc.Add(strEnc)
                        arlsSubnet.Add(strSubnet)
                        arlsTag.Add(strTag)
                        arlsEventP.Add(strEventP)
                        arlsEventYN.Add(strEventYN)
                    End If
                    Row += 1
                Loop Until strName = ""

            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try

            xlWB.Close()
            xlApp.Quit()

        ElseIf (o.ShowDialog = Windows.Forms.DialogResult.Cancel) Then
            MsgBox("You did not select any xls file, the program will now
close")
            Application.Exit()
        End If

14 Jul 2006 11:45 PM

Terry

Looks to me like you need to remove the .ToString from the offending line.
Look at the line just above it, which apparently works and which is a string
to which you can append "\" & strfilename.

--
Terry

Show quoteHide quote

"dotnetnoob" wrote:

> i got this program that will fetch the data in the excel spreadsheet, it was
> working before then i make some adjustment and it now give me an error of
> "Object reference not set to an instance of an object error" i can't seem to
> see what's wrong with my code so i'm hoping some fresh eyes can spot what's
> wrong or what i'm missing. the following is the code, this line   strFile =
> CStr(xlSheet.Range("I" + CStr(Row)).Value.ToString + "\" + strfilename) gave
> me the object reference not set error. it suppose to fetch the data from
> spreadsheet and tell it what directory to copy the file from the I column of
> the spreadsheet contain the directory on a server.
>
> any help is much appericated
>
> thank
>
> Dim o As OpenFileDialog
>         Dim xlApp As Microsoft.Office.Interop.Excel.Application
>         Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
>         Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
>         Dim strName As String
>         Dim strAddress As String
>         Dim strType As String
>         Dim strFile As String
>         Dim strEnc As String
>         Dim strSubnet As String
>         Dim strTag As String
>         Dim strEventP As String
>         Dim strEventYN As String
>         Dim Row As Integer = 2
>
>         arlsName = New ArrayList
>         arlsAddress = New ArrayList
>         arlsType = New ArrayList
>         arlsFile = New ArrayList
>         arlsEnc = New ArrayList
>         arlsSubnet = New ArrayList
>         arlsTag = New ArrayList
>         arlsEventP = New ArrayList
>         arlsEventYN = New ArrayList
>
>         o = New OpenFileDialog
>         o.Title = "Open Excel spreadsheet"
>         o.InitialDirectory = "C:\"
>         o.FilterIndex = 2
>         o.RestoreDirectory = True
>         o.Filter = "All Files (*.xls)|*.xls"
>
>         If (o.ShowDialog = Windows.Forms.DialogResult.OK) Then
>             lblDisplay.Text = o.FileName
>             xlApp = CType(CreateObject("Excel.Application"),
> Microsoft.Office.Interop.Excel.Application)
>             xlApp.Visible = False
>             xlApp.Workbooks.Open(o.FileName)
>             xlWB = xlApp.ActiveWorkbook
>             xlSheet = CType(xlWB.Sheets(2),
> Microsoft.Office.Interop.Excel.Worksheet)
>
>             Dim strfilename As String = "logicaldevice.xml"
>
>             Try
>                 Do
>                     strName = CStr(xlSheet.Range("A" + CStr(Row)).Value)
>                     strAddress = CStr(xlSheet.Range("C" + CStr(Row)).Value)
>                     strType = CStr(xlSheet.Range("F" + CStr(Row)).Value)
>                     strFile = CStr(xlSheet.Range("I" +
> CStr(Row)).Value.ToString + "\" + strfilename)
>                     strEnc = CStr(xlSheet.Range("E" + CStr(Row)).Value)
>                     strSubnet = CStr(xlSheet.Range("D" + CStr(Row)).Value)
>                     strTag = CStr(xlSheet.Range("H" + CStr(Row)).Value)
>                     strEventP = CStr(xlSheet.Range("L" + CStr(Row)).Value)
>                     strEventYN = CStr(xlSheet.Range("K" + CStr(Row)).Value)
>
>                     If strName IsNot Nothing Then
>                         arlsName.Add(strName)
>                         arlsAddress.Add(strAddress)
>                         arlsType.Add(strType)
>                         arlsFile.Add(strFile)
>                         arlsEnc.Add(strEnc)
>                         arlsSubnet.Add(strSubnet)
>                         arlsTag.Add(strTag)
>                         arlsEventP.Add(strEventP)
>                         arlsEventYN.Add(strEventYN)
>                     End If
>                     Row += 1
>                 Loop Until strName = ""
>
>             Catch ex As Exception
>                 MessageBox.Show(ex.ToString)
>             End Try
>
>             xlWB.Close()
>             xlApp.Quit()
>
>         ElseIf (o.ShowDialog = Windows.Forms.DialogResult.Cancel) Then
>             MsgBox("You did not select any xls file, the program will now
> close")
>             Application.Exit()
>         End If

16 Jul 2006 5:02 PM

Cor Ligthert [MVP]

DotNetNoob,

Set in top of your program
Option Strict On,

than it will probably give it you at design time.

I see this because in VBNet is this not done.

"A" + whatever

The concatination character is

"A" & whatever

This + can give errors with option strict of.

Cor

Show quoteHide quote

"dotnetnoob" <dotnetn***@discussions.microsoft.com> schreef in bericht
news:B25E2138-5353-444A-BBA5-A96B76E642D8@microsoft.com...
>i got this program that will fetch the data in the excel spreadsheet, it
>was
> working before then i make some adjustment and it now give me an error of
> "Object reference not set to an instance of an object error" i can't seem
> to
> see what's wrong with my code so i'm hoping some fresh eyes can spot
> what's
> wrong or what i'm missing. the following is the code, this line   strFile
> =
> CStr(xlSheet.Range("I" + CStr(Row)).Value.ToString + "\" + strfilename)
> gave
> me the object reference not set error. it suppose to fetch the data from
> spreadsheet and tell it what directory to copy the file from the I column
> of
> the spreadsheet contain the directory on a server.
>
> any help is much appericated
>
> thank
>
> Dim o As OpenFileDialog
>        Dim xlApp As Microsoft.Office.Interop.Excel.Application
>        Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
>        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
>        Dim strName As String
>        Dim strAddress As String
>        Dim strType As String
>        Dim strFile As String
>        Dim strEnc As String
>        Dim strSubnet As String
>        Dim strTag As String
>        Dim strEventP As String
>        Dim strEventYN As String
>        Dim Row As Integer = 2
>
>        arlsName = New ArrayList
>        arlsAddress = New ArrayList
>        arlsType = New ArrayList
>        arlsFile = New ArrayList
>        arlsEnc = New ArrayList
>        arlsSubnet = New ArrayList
>        arlsTag = New ArrayList
>        arlsEventP = New ArrayList
>        arlsEventYN = New ArrayList
>
>        o = New OpenFileDialog
>        o.Title = "Open Excel spreadsheet"
>        o.InitialDirectory = "C:\"
>        o.FilterIndex = 2
>        o.RestoreDirectory = True
>        o.Filter = "All Files (*.xls)|*.xls"
>
>        If (o.ShowDialog = Windows.Forms.DialogResult.OK) Then
>            lblDisplay.Text = o.FileName
>            xlApp = CType(CreateObject("Excel.Application"),
> Microsoft.Office.Interop.Excel.Application)
>            xlApp.Visible = False
>            xlApp.Workbooks.Open(o.FileName)
>            xlWB = xlApp.ActiveWorkbook
>            xlSheet = CType(xlWB.Sheets(2),
> Microsoft.Office.Interop.Excel.Worksheet)
>
>            Dim strfilename As String = "logicaldevice.xml"
>
>            Try
>                Do
>                    strName = CStr(xlSheet.Range("A" + CStr(Row)).Value)
>                    strAddress = CStr(xlSheet.Range("C" + CStr(Row)).Value)
>                    strType = CStr(xlSheet.Range("F" + CStr(Row)).Value)
>                    strFile = CStr(xlSheet.Range("I" +
> CStr(Row)).Value.ToString + "\" + strfilename)
>                    strEnc = CStr(xlSheet.Range("E" + CStr(Row)).Value)
>                    strSubnet = CStr(xlSheet.Range("D" + CStr(Row)).Value)
>                    strTag = CStr(xlSheet.Range("H" + CStr(Row)).Value)
>                    strEventP = CStr(xlSheet.Range("L" + CStr(Row)).Value)
>                    strEventYN = CStr(xlSheet.Range("K" + CStr(Row)).Value)
>
>                    If strName IsNot Nothing Then
>                        arlsName.Add(strName)
>                        arlsAddress.Add(strAddress)
>                        arlsType.Add(strType)
>                        arlsFile.Add(strFile)
>                        arlsEnc.Add(strEnc)
>                        arlsSubnet.Add(strSubnet)
>                        arlsTag.Add(strTag)
>                        arlsEventP.Add(strEventP)
>                        arlsEventYN.Add(strEventYN)
>                    End If
>                    Row += 1
>                Loop Until strName = ""
>
>            Catch ex As Exception
>                MessageBox.Show(ex.ToString)
>            End Try
>
>            xlWB.Close()
>            xlApp.Quit()
>
>        ElseIf (o.ShowDialog = Windows.Forms.DialogResult.Cancel) Then
>            MsgBox("You did not select any xls file, the program will now
> close")
>            Application.Exit()
>        End If