Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

error opening excel file from vb.net

Posted on 2010-08-31
7
Medium Priority
?
301 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

885 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