Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error opening excel file from vb.net

Posted on 2010-08-31
7
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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