Solved

error opening excel file from vb.net

Posted on 2010-08-31
7
293 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

864 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

24 Experts available now in Live!

Get 1:1 Help Now