Solved

error opening excel file from vb.net

Posted on 2010-08-31
7
295 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Gridview selected row 9 49
VB.Net - KeyPress Event 4 38
SQL LINE CONTINUATION ISSUE 12 38
Sql server, import complete table, using vb.net 9 43
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

838 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