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

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
LVL 1
chrisfixitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shahid ThaikaSole ProprietorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrisfixitAuthor Commented:
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
Shahid ThaikaSole ProprietorCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chrisfixitAuthor Commented:
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
Shahid ThaikaSole ProprietorCommented:
On which line is the error raised, before it hits the Exception area
0
chrisfixitAuthor Commented:
myWorkbook.SaveAs(xfilname & "m", FileFormat:=52

Error 'Document not saved' error code -2146827284.
0
Shahid ThaikaSole ProprietorCommented:
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
chrisfixitAuthor Commented:
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
chrisfixitAuthor Commented:
This is the error when I don't catch the exception.
0
Shahid ThaikaSole ProprietorCommented:
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
chrisfixitAuthor Commented:
Thanks very much for all your help and I'll post back if it works or I discover a solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.