[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I use Return with Try..Catch..Finally in VB Net

Posted on 2010-03-22
11
Medium Priority
?
605 Views
Last Modified: 2013-11-26
I have a routine which saves xls files as xlsx..

occasionally it finds a corrupt xls file which throws an exception..

I want to print an error to a text file and continue processing .. but if I add the Try..Catch.. Construct I end up with a problem with te Return..

Private Function XcelPr(ByVal xfilname As String, ByVal actual As String) As String

        savefile = xfilname.Replace(pathname, spathname)
        Dim newdirty As String = savefile.Replace(actual, "")
        Directory.CreateDirectory(newdirty)
        savehere = savefile.Replace(".xls", ".bak")
        Dim myWorkbook As Excel.Workbook = excApp.Workbooks.Open(xfilname)
        tempname = xfilname & "m"
        If Not System.IO.File.Exists(tempname) Then
            tempname = xfilname & "x"
            If Not System.IO.File.Exists(tempname) Then
                myWorkbook.SaveAs(xfilname & "m", FileFormat:=52)
            End If
        End If
        If Not myWorkbook Is Nothing Then
            myWorkbook.Close()
            Marshal.FinalReleaseComObject(myWorkbook)
        End If
        noxls += 1
        ToolStripStatusLabel1.Text = noxls.ToString & " Excel files processed.. running"
        Application.DoEvents()
        Return savehere
    End Function

Ideas please
0
Comment
Question by:chrisfixit
  • 6
  • 5
11 Comments
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 2000 total points
ID: 28316442
Perhaps something as the code below will solve your purpose...
Private Function XcelPr(ByVal xfilname As String, ByVal actual As String) As String
        'I added the follow declarations - eeshahidt
        Dim savefile As String
        Dim savehere As String
        Dim tempname As String
        'end edits

        Dim bError As Boolean

        Try

        
            savefile = xfilname.Replace(pathname, spathname)
            Dim newdirty As String = savefile.Replace(actual, "")
            Directory.CreateDirectory(newdirty)
            savehere = savefile.Replace(".xls", ".bak")
            Dim myWorkbook As Excel.Workbook = excApp.Workbooks.Open(xfilname)
            tempname = xfilname & "m"
            If Not System.IO.File.Exists(tempname) Then
                tempname = xfilname & "x"
                If Not System.IO.File.Exists(tempname) Then
                    myWorkbook.SaveAs(xfilname & "m", FileFormat:=52)
                End If
            End If
            If Not myWorkbook Is Nothing Then
                myWorkbook.Close()
                Marshal.FinalReleaseComObject(myWorkbook)
            End If
            noxls += 1
            ToolStripStatusLabel1.Text = noxls.ToString & " Excel files processed.. running"
            Application.DoEvents()
        Catch ex As Exception
            bError = True
        Finally
            'do clean up here

        End Try

        If bError Then
            'if error
            Return "Something else?"
        Else
            'if no error
            Return savehere
        End If
    End Function

Open in new window

0
 
LVL 1

Author Comment

by:chrisfixit
ID: 28410438
I'm having a problem with this..when the exception is caught I need to close the corrupt file.. if I try to do this
another exception is thrown..if I just return from the routine and continue to try and process the remaining files, every subsequent open throws an exception..

.. I don't know how to handle the exception so I can ignore the corrupt file and continue normal processing.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 28413686
Any clean up activity, including closing of file should come in between FINALLY and END TRY. In case it still does not work, try declaring the variables outside/before the beginning of TRY block. You can also replace "Exception" in "Catch ex As Exception", with the actual exception you are trying to catch. In other words, you are expecting an error and you want your software to perform certain code when that error occurs.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Author Comment

by:chrisfixit
ID: 28414850
It doesn't seem to matter.. it opens the corrupt file but when it trys to save it as xlsm it fails .. the exception is 'system is unable to open any more threads' or something similar..

Manually opening the file from Excel takes for ever and saving fails giving 'unable to save.. do you want to try and repair?' .. this repair fails.

so in the program, any attempt to close the corrupt file in the Finally section throws an exception, I cannot seem to 'get rid' of the corrupt file in any way without an exception, and if I do nothing, every subsequent open on 'good' file fails.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 28415819
On which line is the error raised, before it hits the Exception area
0
 
LVL 1

Author Comment

by:chrisfixit
ID: 28417394
myWorkbook.SaveAs(xfilname & "m", FileFormat:=52

Error 'Document not saved' error code -2146827284.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 28418528
OK, I am really not sure why the error is happening. Can you post the exact error message for both the first and second. Also, I noticed a programming error...

Where you have...

            If Not System.IO.File.Exists(tempname) Then
                tempname = xfilname & "x"
                If Not System.IO.File.Exists(tempname) Then
                    myWorkbook.SaveAs(xfilname & "m", FileFormat:=52)
                End If
            End If

tempname has a file name ending in 'x', but you are saving a file with an ending 'm'. If your workbook is macro enabled than do 'm', else do 'x'.

Also, before all else, you better have referenced an Excel 2007 object to do SaveAs in 2007 format.
0
 
LVL 1

Author Comment

by:chrisfixit
ID: 28427014
It's not an error :-)

A few of the several hundred xls files have been manually saved as xlxs .. if they exist I don't want to create an xlsm.

I ave 100 test files and it's only this one that causes te problem and as I say Excel won't save it manually either.. the thing is there are about 750 I need to convert and I don't want the program falling over on my client's computer ;-)

Full error message attached...
System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  HelpLink="xlmain11.chm"
  Message="Document not saved."
  Source="Microsoft Excel"
  StackTrace:
       at Microsoft.Office.Interop.Excel.WorkbookClass.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local)
       at FutureKids.Form1.XcelPr(String xfilname, String actual) in C:\Users\FIXIT\Documents\Visual Studio 2008\Projects\FutureKids\FutureKids\Form1.vb:line 152
       at FutureKids.Form1.Recursive(String strPath) in C:\Users\FIXIT\Documents\Visual Studio 2008\Projects\FutureKids\FutureKids\Form1.vb:line 72
       at FutureKids.Form1.Button4_Click(Object sender, EventArgs e) in C:\Users\FIXIT\Documents\Visual Studio 2008\Projects\FutureKids\FutureKids\Form1.vb:line 44
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at FutureKids.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Open in new window

0
 
LVL 1

Author Comment

by:chrisfixit
ID: 28427042
This is the error when I don't catch the exception.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 28436790
Ah, that makes sense. I thought you had a typo since you were searching for one file, but saving another. I am really sorry, this is beyond my knowledge. As a last resort, I'd suggest you put the clean up code within its own try-catch as well...
    Function YourFunction() As String
        Try
            'do something
        Catch ex As Exception
            'catch exception
        Finally
            'do clean up
            Try
                'try advanced clean up
            Catch ex As Exception

            End Try
        End Try
    End Function

Open in new window

0
 
LVL 1

Author Comment

by:chrisfixit
ID: 28437991
Thanks very much for all your help and I'll post back if it works or I discover a solution.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
Today, unlike web development, the mobile landscape is complex enough for a software engineer and Android is posing more challenging environment thanks to its fragmentation issues on hardware and software fronts.
Starting up a Project
Screencast - Getting to Know the Pipeline

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question