Solved

error opening excel file from vb.net

Posted on 2010-08-31
7
292 Views
Last Modified: 2012-05-10
Hello I created the following function to create and save a workbook, which it does.  However, I get an error message stating that the workbook is being used by another process whien I attempt to open it as  in
Dim strWorkbookPath As String = Utilities.CreateWorkbook()
        MessageBox.Show(strWorkbookPath, "Returned Workbook Path")
        Utilities.OpenWorkbook(strWorkbookPath)  ' ERROR Occurs here

I tried killing it but to no avail
Public Shared Function CreateWorkbook() As String

        Dim filename As String



        Dim excelApp As New Excel.Application

        Dim savefiledialog As New SaveFileDialog







        If My.Settings.DefaultLocation = "" Then

            savefiledialog.InitialDirectory = "C:\"

        Else

            savefiledialog.InitialDirectory = My.Settings.DefaultLocation.ToString

        End If



        savefiledialog.Filter = "xlsx files (*.xlsx)|*.xlsx"

        savefiledialog.FilterIndex = 2

        savefiledialog.RestoreDirectory = True

        'Try

        '***************************Creates Spreadsheet*********************************************



        If savefiledialog.ShowDialog = Windows.Forms.DialogResult.OK Then

            filename = savefiledialog.FileName



            'Validate file name 

            If Convert.ToString(filename) = "" Then

                Return Nothing

                Exit Function

            End If



            'Create excel instance 



            Dim excelWorkbook As Excel.Workbook

            Dim excelSheet As Excel.Worksheet

            Dim misValue As Object = System.Reflection.Missing.Value

            excelApp = New Excel.ApplicationClass

            'excelApp.Visible = True



            excelWorkbook = excelApp.Workbooks.Add(misValue)

            excelSheet = excelWorkbook.Sheets("sheet1")

            excelApp.ScreenUpdating = True

            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)

            excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper

            If Not filename.EndsWith(".xlsx") Then

                excelWorkbook.SaveAs(filename, 56)



            Else

                excelWorkbook.SaveAs(filename)

            End If



            MsgBox("File generated successfully at " & filename)

            Return filename

        Else

            Return Nothing



        End If

        KillExcel(excelApp)



    End Function

Open in new window

0
Comment
Question by:chtullu135
  • 3
  • 3
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33572731
shouldnt you be closing excel properly?

close the workbook    excelWorkbook.close
quit excel excelApp.quit

maybe set a variable and assign filename or nothing to that variable
close excel properly then return that variable - code should be where you got killexcel
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33572750
quick change of code, note I removed New Excel.Application as not required. You may return and leave that object created, total waste


Public Shared Function CreateWorkbook() As String
       
        Dim filename As String
        Dim excelApp As Excel.Application
        Dim excelWorkbook As Excel.Workbook
        Dim excelSheet As Excel.Worksheet
        Dim SaveFileDialog As New SaveFileDialog



        If My.Settings.DefaultLocation = "" Then
            SaveFileDialog.InitialDirectory = "C:\"
        Else
            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.toString
        End If

        SaveFileDialog.filter = "xlsx files (*.xlsx)|*.xlsx"
        SaveFileDialog.FilterIndex = 2
        SaveFileDialog.RestoreDirectory = True
       
        'Try
        '***************************Creates Spreadsheet*********************************************

        If SaveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
           
            filename = SaveFileDialog.filename

            'Validate file name
            If Convert.toString(filename) = "" Then
                Return Nothing
                Exit Function
            End If

            'Create excel instance
            Dim misValue As Object = System.Reflection.Missing.Value
            excelApp = New Excel.ApplicationClass
            'excelApp.Visible = True

            excelWorkbook = excelApp.Workbooks.Add(misValue)
            excelSheet = excelWorkbook.Sheets("sheet1")
            excelApp.ScreenUpdating = True
            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)
            excelApp.Caption = System.Guid.NewGuid.toString.ToUpper
            If Not filename.EndsWith(".xlsx") Then
                excelWorkbook.SaveAs(filename, 56)
            Else
                excelWorkbook.SaveAs (filename)
            End If

            MsgBox ("File generated successfully at " & filename)
           

****> CLOSE DOWN PROPERLY HERE
            excelWorkbook.Close
            excelApp.Quit
           
            Set excelWorkbook = Nothing
            Set excelApp = Nothing
            Return filename
        Else
            Return Nothing

        End If
        KillExcel (excelApp)

    End Function

0
 

Author Comment

by:chtullu135
ID: 33573247
If I put it a try catch block then I could put the following in the finally statement
excelWorkbook.Close
            excelApp.Quit
           
            Set excelWorkbook = Nothing
            Set excelApp = Nothing
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 33576671
0
 

Author Comment

by:chtullu135
ID: 33577054
Thanks for the info.  I've also read the linked article on backward compatibility.  I've run into that issue before, particulary between 64 bit SQL server and Excel 2007
0
 

Author Closing Comment

by:chtullu135
ID: 33577071
Thanks
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33577118
>> If I put it a try catch block then I could put the following in the finally statement

yes you could but need to be careful in case you get an exception before the workbook has been opened so maybe check the objects have been instantiated or something.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now