Dialog Box Issues

[cosmarchy]

Hello all,

I am having trouble with some code I am writing to handle an Excel spreadsheet. The idea is that the dialog box is displayed which invites the user to select an Excel spreadsheet. Once they have done that, it is opened and a separate function checks to see whether Sheet1 exists returning Nothing if it is not found. The intention being the try/catch block picks it up and gracefully does nothing.

The problem is that when the function returns nothing, the dialog box is still displayed and the sub doesn't seem to exit.

The code is here:

Private Sub ofdFileToTranslate_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ofdFileToTranslate.FileOk

        TextBox1.Text = ofdFileToTranslate.FileNames(0)

        Dim ws As Excel.Worksheet

        objExcelApp = New Excel.Application
        objExcelApp.Visible = False

        Try
            ws = FindSheet("Sheet1", objExcelApp.Workbooks.Open(TextBox1.Text))
            
            'do something...

        Catch ex As NullReferenceException

            Console.WriteLine("NullReferenceException Caught")

        End Try

    End Sub

Can anyone tell me where I am going wrong or offer any enhancements?

Many thanks

Cos

 

[jmcilhinney]

You shouldn't be catching a NullReferenceException. You should be testing for Nothing first and then not trying to do whatever throws the exception if you find it.

 

[cosmarchy]

You shouldn't be catching a NullReferenceException. You should be testing for Nothing first and then not trying to do whatever throws the exception if you find it.

Nope....still can't get anything to work. Even after checking for nothing, the dialog is still displaying asking for a file to open.

The revised code:

Private Sub ofdFileToTranslate_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ofdFileToTranslate.FileOk

        TextBox1.Text = ofdFileToTranslate.FileNames(0)

        Dim ws As Excel.Worksheet

        objExcelApp = New Excel.Application
        objExcelApp.Visible = False

        ws = FindSheet("Sheet1", objExcelApp.Workbooks.Open(TextBox1.Text))

        if (not ws is nothing) then
            
            'do something...

       end if

    End Sub

and the FindSheet function code:

    Public Function FindSheet(ByVal strSheetName As String, ByVal objWorkBook As Excel.Workbook) As Excel.Worksheet

        If (Not objWorkBook.Worksheets(strSheetName) Is Nothing) Then
            Return objWorkBook.Worksheets(strSheetName)
        Else
            Return Nothing
        End If

    End Function

Can anyone please advise further.

Thanks

 

[jmcilhinney]

You're obviously testing the wrong reference for Nothing then. You need to debug your original code. Determine exactly what reference is Nothing first, and then you can determine why.

 

[roccoman]

if (not ws is nothing) then

'do something...

end if

shouldnt that be

        if not [B][COLOR="red"]([/COLOR][/B]ws is nothing) then
            
            'do something...

       end if

and here in your function:

        If (Not objWorkBook.Worksheets(strSheetName) Is Nothing) Then
            Return objWorkBook.Worksheets(strSheetName)
        Else
            Return Nothing
        End If

if you step through this are you sure that the "Return Nothing" is returning the value?


also variable ws - when you dim it, set it = nothing to ensure that it truly is nothing and remains that way until it is filled with a legit object